VBA Delete Sheet

Excel VBA Delete Sheet

To Delete Sheet in VBA we a Delete Worksheet method. To apply this method first we need to identify which sheet we are deleting by calling the sheet name. We have two methods to do the same first we directly write sheet1.delete and second method is sheets(sheet1).delete.

So the syntax follows.

Worksheets("Worksheet Name").Delete

OR

Sheets("Sheet Name").Delete

So, first, we need to specify the sheet name by using either Worksheet or Sheets Object, then later, we can use the “Delete” method.

How to Delete Excel Sheets using VBA Code?

Example #1 – Delete Worksheet by using its Name

Assume you have many sheets, and to delete the particular worksheet, we need to mention the worksheet by its name. For example, I have 3 different sheets named “Sales 2016”, “Sales 2017”, and “Sales 2018”.

If I want to delete the sheet named “Sales 2017,” then I have to mention the sheet name like the below.

Code:

Sub Delete_Example1()

  Worksheets("sheets 2017").

End Sub

The problem with mentioning the worksheet name directly we don’t get to see the IntelliSense list of VBA. Anyways mention the method as “Delete.”

Code:

Sub Delete_Example1()

  Worksheets("sheets 2017").Delete

End Sub

So this will delete the sheet named “Sales 2017“.

Error While Deleting the Worksheet: If we try to delete the worksheet which does not exist or if we mention the worksheet name wrongly, then we will get the VBA error as “Subscript Out of Range.”

In the above, I got the “Subscript Out of Range” error because, in my workbook, there is no sheet name called “Sales 2017”.

Example #2 – Delete Worksheet by its Name with Variables

As we have seen in the above example, the moment we refer to the worksheet name by using the Worksheets object, we don’t get to see the IntelliSense list. To get to see the IntelliSense list, we need to use variables.

Step 1: First, declare the variable as Worksheet.

Code:

Sub Delete_Example2()

  Dim Ws As Worksheet

End Sub

Step 2: Since the worksheet is an object variable, we need to set the variable to the specific worksheet by using the “SET” word.

Code:

Sub Delete_Example2()

  Dim Ws As Worksheet

  Set Ws = Worksheets("Sales 2017")

End Sub

Now the variable “Ws” refers to the worksheet named “Sales 2017”.

Step 3: Now, using the variable “Ws,” we can access all the IntelliSense list of the worksheet.

Code:

Sub Delete_Example2()

  Dim Ws As Worksheet

  Set Ws = Worksheets("Sales 2017")
  
  Ws.

End Sub

Step 4: From the IntelliSense list, select the “Delete” method.

Code:

Sub Delete_Example2()

  Dim Ws As Worksheet

  Set Ws = Worksheets("Sales 2017")

  Ws.Delete

End Sub

Like this using variables, we can access the IntelliSense list.

Example #3 – Delete Active Worksheet

Active Sheet is nothing but whichever worksheet active or selected at the moment. For this method, we need not mention the worksheet name. For example, look at the below VBA code.

ActiveSheet.Delete

Right now, the active sheet is “Sales 2017”.

If I run the code, it will delete the active sheet, I.e., “Sales 2017”.

Now I will select “Sales 2016”.

Now it will delete the active sheet i.e., “Sales 2016”.

Like this, we can use the “Active Sheet” object to delete the worksheet.

Note: To use this method, we need to absolutely sure of what we are doing with the Active Sheet and which sheet is going to be an active sheet.

Example #4 – Delete More than One Worksheet

In our above examples, we have seen how to delete a single sheet, but what if we have multiple worksheets? Let’s say we want to delete 10 worksheets.

We cannot keep writing 10 lines of code to delete the worksheet, so we need to use loops to loop through the collection of worksheets and delete them.

The below code will loop through the worksheets and delete all the worksheets in the workbook.

Code:

Sub Delete_Example2()

  Dim Ws As Worksheet

  For Each Ws In ActiveWorkbook.Worksheets
    Ws.Delete
  Next Ws

End Sub

The above code will throw an error because it attempts to delete all the sheets in the workbook. So to avoid this, we need to retain at least one worksheet.

If we want to delete all the worksheets except the active sheet, then we need to use the below code.

Code:

Sub Delete_Example2()

  Dim Ws As Worksheet

  For Each Ws In ActiveWorkbook.Worksheets
    If ActiveSheet.Name <> Ws.Name Then
      Ws.Delete
    End If
  Next Ws

End Sub

Similarly, if we don’t want to delete a specific worksheet but to delete all other worksheets, then we can use the below code.

Code:

Sub Delete_Example2()

 Dim Ws As Worksheet

 For Each Ws In ActiveWorkbook.Worksheets
   If Ws.Name <> "Sales 2018" Then 'You can change the worksheet name
     Ws.Delete
   End If
 Next Ws

End Sub

The above code will delete all the worksheets except the worksheet named “Sales 2018”.

Recommended Articles

This has been a guide to VBA Delete Sheet. Here we learn how to delete worksheets using VBA coding along with some practical examples and a downloadable excel template. You may also have a look at other articles related to Excel VBA –

• What is VBA Message Box (Yes/No)?
• VBA Delete File
• Delete Row in VBA
• Protect Sheet with Excel VBA
• VBA ByRef