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


fresher interview questions and answersexcel 2007 checkboxhow to create combobox in excel 2010microsoft excel developer tabhow to merge data from multiple worksheets in excelexcel vba isnavba select a worksheetloop vba codevba sortingrun a query in excelworking with arrays in excel vbaapplication inputboxexcel remove rows containingexcel vba selection objectcountif in excel exampleexcel vba color codesmonthly planning calendar template excelms excel hlookup examplevba stepexcel vba pivot table refreshexcel macro columnmacro in xlsmicrosoft excel substituteamazing excel formulasvba create userformhow do you hide columns in excelexcel formula sumifvba create formexcel macro querydelete duplicate data excelmultiple if function excel 2010access vba sleepexcel weeks between two datesexcel vba copy rangeapplication.goto referenceremove password from excel workbookconditional vlookupexcel macro conditional statementsort listbox vbaswitch vbavb 6.0 interview questions and answers for experiencedsheet vbaexcel carriage returnhow to hide sheets in excel3d column chart excellistbox exceldelete entire row vbaexcel vba lookup functionsort worksheets in excelvba integer to stringvba rowvba sql serverrange macro excelvba code for combobox selectionexcel identify duplicate rowsvba coding tutorialddl and dmlunshare excel spreadsheetdrop down menu in excel 2007ms excel remove duplicate rowsvba set column widthexcel 2010 unhide worksheetmerging columns in excelbeginner excellearn vba codingexcel delete duplicatesexcel pivot table vbausing excel functions in vbaexplanation of excel formulassimple vb projects for beginnersexcel vba chart objectvb messageboxformula vba excelredim preserve array vbaadd developer tab to excel 2007excel vba range clearexcel vba insert rowshow to make if statements in excelscheduling in excel templatevba open xls filedelete duplicate columns in excelhow to use sumif function in excelhow to delete repeats in exceldrop down list excel 2007inserting a column in excelhow to hide cells in excellremove duplicates excel 2013