How to Delete Excel Sheet Using VBA (10 VBA Codes)

Get FREE Advanced Excel Exercises with Solutions!

Microsoft Excel VBA (Visual Basic for Applications) is such a tool that you can do any operation in Excel using it. We can simply delete sheets manually but if we have a lot of sheets then we can delete the sheets smartly and quickly in various ways using VBA. This article will provide you with 10 easy ways to delete Excel sheet using VBA with vivid illustrations.


How to Delete Sheet in Excel Using VBA: 10 VBA Codes

Let’s get introduced to our dataset first. I have placed some product names and their corresponding product codes and prices in my dataset.


1. Delete an Excel Sheet Using the Sheet Name in Excel VBA

In our very first way, we’ll delete a sheet using its name in VBA codes. It’s quite easy. Our sheet name is ‘Sales (1)’. Let’s see how to do it.

Steps:

  • Right-click on the sheet title.
  • Select View Code from the context menu.
  • A VBA window will open up.

Or you can press Alt+F11 to open the VBA window directly.

Delete an Excel Sheet Using the Sheet Name in Excel VBA

  • Type the following codes.
Sub delete_sheet()
Sheets("Sales (1)").Delete
End Sub
  • Then just press the Play icon to run the codes.

A warning dialog box will appear to ensure deletion.

Delete an Excel Sheet Using the Sheet Name in Excel VBA

  • At this moment just press Delete. You will notice that the sheet is deleted.

Read More: Shortcut to Delete Sheet in Excel


2. Remove Excel Sheet Using the Sheet Number Using VBA

If you like to use the sheet numbers instead of using the sheet name to delete the sheet, then it is possible to do it in VBA.

Steps:

  • Like the previous example, open the VBA window for the sheet.
  • Later, type the following codes-
Sub delete_sheet()
Sheets(2).Delete
End Sub
  • Then, press the Play icon to run the codes.

A warning dialog box will appear

Remove Excel Sheet Using the Sheet Number Using VBA

  • Now just press Delete. And the sheet will be deleted permanently.


3. Delete the Active Sheet in Excel Using VBA

We can delete only the active sheet using VBA too.

Steps:

  • Open the VBA window for your current active sheet, like the first example.
  • After that, write the given codes in it-
Sub delete_ActiveSheet()
ActiveSheet.Delete
End Sub
  • Next, press the Play icon.

Delete the Active Sheet in Excel Using VBA

  • Then, a warning dialog box will appear; you will have to press Delete

Delete the Active Sheet in Excel Using VBA


4. Delete All Sheets Except the Active Sheet Applying Excel VBA

Suppose you have a lot of sheets in your workbook, but you want to delete the sheets except your current active sheet then we can code in VBA to do it.

Steps:

  • Open up the VBA window for your current active sheet, like the first example.
  • After that, write the given codes in it-
Sub DeleteExcepActiveSheet()
Dim x As Worksheet
For Each x In ThisWorkbook.Worksheets
If x.Name <> ActiveSheet.Name Then
x.Delete
End If
Next x
End Sub
  • Next, press the Play icon to run the codes.

A dialog box named ‘Macros’ will appear.

Delete All Sheets Except the Active Sheet Applying Excel VBA

  • Select DeleteExceptActiveSheet.
  • Finally, press Run.

Delete All Sheets Except the Active Sheet Applying Excel VBA

  • Then the warning dialog box will appear one by one for every sheet, Keep pressing Delete.

Read More: How to Delete All Sheets in Excel Except One


5. Remove Sheet after Checking If It Exists Using VBA in Excel

It becomes safer if we can check a sheet before deleting it. In this example, we’ll show you how to delete sheet if it exists. For that, we’ll code in a different way where we can manually put the sheet name in a command box to delete.

Steps:

  • Open the VBA window like the first example.
  • Write the following codes in it-
Sub check_if_delete()
Dim x As Worksheet
Dim xSheet As Variant
xSheet = InputBox("Input Sheet Name")
Application.DisplayAlerts = False
For Each x In ThisWorkbook.Worksheets
    If xSheet = x.Name Then
      x.Delete
    End If
Next x
Application.DisplayAlerts = True
End Sub
  • Press the Play icon to run the codes.

A command box named ‘Input Sheet Name’ will appear.

Remove Sheet after Checking If It Exists Using VBA in Excel

  • Now type the sheet name and press OK.

Remove Sheet after Checking If It Exists


6. Delete All the Worksheets in the Workbook Applying VBA in Excel

Suppose you have made some mistakes making the worksheets, and now you want to delete all the sheets instead of opening a new one, it is time-consuming to delete the sheets manually. We can delete all sheets using VBA very quickly.

Steps:

  • Open the VBA window for any sheet of your workbook.
  • Then, write the following codes-
Sub delete_all_worksheets()
Dim x As Worksheet
Dim xSheet As String
xSheet = "NewBlankSheet-" & Format(Now, "SS")
Sheets.Add.Name = xSheet
Application.DisplayAlerts = False
For Each x In ThisWorkbook.Worksheets
    If x.Name <> mySheet Then
      x.Delete
    End If
Next x
Application.DisplayAlerts = True
End Sub
  • Finally, just press the Play icon, and all the sheets will be deleted at a time.

Delete All the Worksheets in the Workbook Applying VBA in Excel


7. Remove All Sheets with a Specific Word Using Excel VBA

It is possible to delete sheets whose names contain specific words. In this example, I’ll delete the sheets which contain ‘7’.

Steps:

  • Open up the VBA
  • Later, write the following codes in it-
Sub DeleteWorksheetsWith7()
Dim x As Worksheet
Application.DisplayAlerts = False
For Each x In ThisWorkbook.Worksheets
If InStr(x.Name, "7") Then
x.Delete
End If
Next x
Application.DisplayAlerts = True
End Sub
  • Next, just press the Play icon to run the codes.

Remove All Sheets with a Specific Word

Now take a look that the sheet containing ‘7’ is gone.

Remove All Sheets with a Specific Word


8. Delete Worksheet without Any Warning Message Applying Excel VBA

We noticed in some previous examples that we face warning messages before deleting them. Sometimes, it gets bothering, so we can avoid it using VBA.

Steps:

  • Open up the VBA
  • Write the following codes in it-
Sub Delete_WithoutWarningMessage()
    Application.DisplayAlerts = False
    Sheets("Sales (8)").Delete
    Application.DisplayAlerts = True
End Sub
  • Then, press the Play icon to run the codes.

Delete Worksheet without Any Warning Message

  • At this moment, select the macro Delete_WithoutWarningMessage and press Run.

Delete Worksheet without Any Warning Message

Now, you will see that the sheet ‘Sales (8)’ is deleted without warning.

Delete Worksheet without Any Warning Message


9. Remove Multiple Sheets in Excel Using VBA

While deleting, sometimes we need to delete specific multiple sheets. I’ll delete two sheets named ‘Sales (1)’ and ‘Sales (2)’.  Let’s see how to do it using VBA.

Steps:

  • Open up the VBA
  • Write the following codes in the window-
Sub deleteMultipleSheets()
    Dim mySheetNames() As Variant
    mySheetNames = Array("Sales (1)", "Sales (2)")
    Application.DisplayAlerts = False
    ThisWorkbook.Sheets(mySheetNames).Delete
    Application.DisplayAlerts = True
End Sub
  • Finally, press the Play icon to run the codes and the multiple sheets will be removed.

Remove Multiple Sheets in Excel

Read More: Delete Multiple Sheets in Excel


10. Remove All Hidden Sheets in Excel Using VBA

Excel has the option to hide sheets, and we can delete the hidden sheets using VBA without unhiding them. First, I’ll hide a sheet.

Steps:

  • Right-click on the sheet title.
  • Then, select Hide from the context menu.

Remove All Hidden Sheets

  • Now right-click on any sheet title of your workbook and select View Code from the context menu.

Remove All Hidden Sheets

  • Now type the following codes-
Sub DeleteHiddenWorksheets()
Dim x As Worksheet
Application.DisplayAlerts = False
For Each x In ThisWorkbook.Worksheets
If x.Visible <> True Then
x.Delete
End If
Next x
Application.DisplayAlerts = True
End Sub
  • Finally, just press the Play icon to run the codes.

Now take a look that there is no sheet to unhide, which means the hidden sheets are deleted.

Remove All Hidden Sheets

Read More: How to Delete Hidden Sheets in Excel


Download Practice Workbook

You can download the free Excel template from here and practice on your own.


Conclusion

I hope all of the methods described above will be good enough to delete sheets in Excel using VBA. Feel free to ask any questions in the comment section, and please give me feedback.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo