How to Delete Excel Sheet Using VBA (10 Suitable Ways)

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.

Download Practice Workbook

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

10 VBA Codes to Delete Sheet in Excel

Let’s get introduced to our dataset first. I have placed some products 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.

• 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 deleting.

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

Read More: Shortcut to Delete Sheet in Excel (5 Quick Examples)

2. Remove Excel Sheet Using the Sheet Number Using VBA

If you like to use sheet number instead of using sheet name to delete 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 just press the Play icon to run the codes.

A warning dialog box will appear

• 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.

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

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, just press the Play icon to run the codes.

A dialog box named ‘Macros’ will appear.

• Select DeleteExceptActiveSheet.
• Finally, just press Run.

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

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 code in a different way where we’ll be able to put the sheet name manually 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.

• Now type the sheet name and press OK.

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 then 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.

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.

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

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.

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

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

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.

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. At first, I’ll hide a sheet.

Steps:

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

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

• 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.

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 question in the comment section and please give me feedback.

Related Articles

• How to Delete a Sheet in Excel (5 Ways)
• Delete Multiple Sheets in Excel (4 Ways)