Delete Method of Worksheet Object VBA

Delete Worksheet method in VBA is used to delete the sheet from the Excel workbook. When we delete a worksheet, Delete worksheet method displays a dialog box that reminds the user to confirm the deletion by default. If we click on Delete button on the dialog box then it deletes the worksheet from a workbook. It has Boolean value. That means dialog box will appear with two options. That is either Cancel(False) or Delete(True).

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


Why we use Delete Worksheet method in VBA?

Sometimes we may don’t want the some of the worksheets in a workbook. We may thought to delete those worksheets. During that time we can use Delete worksheet method to delete the worksheets.

VBA Delete Worksheet Method- Syntax

Here is the example syntax to Delete the Worksheet using VBA.

Worksheets(“YourSheetName”).Delete

Where Worksheet represents Object and Delete is the method of worksheet object.

VBA Delete Worksheet Method: Example 1

Please see the below VBA code to Delete Worksheet from a workbook with warning message. When we are deleting worksheet, it will display Excel dialog message and prompts the confirmation message to the user. Once you click on Delete button no more deleted sheet will be available in a workbook. If you click on cancel button, it won’t delete the Worksheet in a workbook.

Sub Delete_Sheet()
Sheets("Sheet2").Delete
‘Or
Sheet2.Delete
End Sub

In the above example we have deleted sheet named ‘Sheet2’ from a workbook using Delete method of worksheet object.

VBA Delete Worksheet Method: Example 2

Please see the below VBA code to Delete active Worksheet from a workbook.

Sub Delete_ActiveSheet()
ActiveSheet.Delete
End Sub

In the above example we have deleted active sheet from a workbook using Delete method of worksheet object.

VBA Delete Worksheet Method: Example 3

Please see the below VBA code to Delete Worksheet from a workbook without any warning message.

Sub Delete_Sheet_WithoutWarningMessage()
Application.DisplayAlerts = False
Sheets("Sheet2").Delete
Application.DisplayAlerts = True
End Sub

In the above example we have deleted Worksheet from a workbook using Delete method of worksheet object. And it will show you how to stop showing warning message while deleting worksheet.

VBA Delete Worksheet Method – Instructions

Please follow the below step by step instructions to execute the above mentioned VBA macros or codes:

  1. Open an Excel Worksheet
  2. Press Alt+F11 to Open the VBA Editor window or Open it from the Developer Tab
  3. Insert a Module from Insert Menu
  4. Copy the above code for activating worksheet and Paste in the code window(VBA Editor)
  5. Save the file as macro enabled Worksheet
  6. Press ‘F5’ to run it or Keep Pressing ‘F8’ to debug the code line by line and then check deleted Worksheet doesnot exist in the Workbook..
LIMITED TIME OFFER

Leave A Comment


Related pages


task tracker excelactivate developer tab in excelhow to unprotect excel worksheetadd drop down in excel 2007vba last cellelseif excelhow to merge fields in excelexcel vba division remainderadvanced pivot table techniquesaccess parameter query vbams excel trimexcel rows vbaleft function in vbause excel to find duplicatesvba waitvba excel read text fileworksheet on coloursprogramming excel vbavba columns selectms excel pivot table tutorialvba messageboxvba filter functionconvert macro to vbaworkbooks.open vbaisblank excelmerge cell in exceldelete cells vbahow do you merge and center cells in excelif function in excel 2007 with examplecollections in vbavba sample projectsmsgbox iconsloop vba codehlookup definitioniserror function in accesswhere is the tick in excelexcel last cell in columntotal no of rows and columns in excelexcel vba sql query exampleexcel formula lookupexcel vba delete entire rowhow to loop a macro in excelinsert check mark in excel 2007pivot table advanceinsert hyperlink in excelms access iserrorcombobox propertiesmsgbox buttonsms access vba functionstimeline templates excelapplying filters in excelvlookup 2010 tutorialexcel shotcutworking with pivot tables in excel 2007project portfolio dashboard templateiferror vlookup excel 2010microsoft excel macros tutorialunprotect excel 2013 workbookvba excel loopexcel macro gotovba programming in excel examplesupdate access table from excel vbabar chart excel templateexcel lock sheeteditable swot analysis templatevba userform textboxexcel 2007 dashboard tutorialpaste special transposeexcel 2010 vba progress barproject budget spreadsheet templateconstruction project excel templatecapacity planning spreadsheet excelhow to remove blank rows in excelsales trend analysis excel templatehow to activate activex controlhow to use count if function in excelexcel pivot table advanced