VBA Delete Sheet

We use a "Delete Worksheet" method to delete a sheet in VBA. 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; the 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.

Example #1 - Delete Worksheet by using its Name

Assume you have many sheets. We need to mention the worksheet's name to delete a particular worksheet. For example, we have 3 different sheets named "Sales 2016", "Sales 2017", and "Sales 2018".

If we want to delete the sheet named "Sales 2017," then we 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 is that we do not get to see the IntelliSense list of VBA. So, 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 that does not exist or mention the worksheet name wrongly, we will get the VBA error as "Subscript Out of Range."

In the above, we got the “Subscript Out of Range” error because, in our 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 Worksheet object, we do not 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 using the “SET” word.

Code:

Sub Delete_Example2()

  Dim Ws As Worksheet

  Set Ws = Worksheets("Sales 2017")

End Sub

The variable "Ws" refers to the "Sales 2017" worksheet.

Step 3: Now, using the variable “Ws,” we can access all the IntelliSense lists 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

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

ActiveSheet.Delete

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

If we run the code, it will delete the active sheet, "Sales 2017."

Now, we will select "Sales 2016."

Now, it will delete the active sheet, "Sales 2016."

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

Note: To use this method, we must be sure what we are doing with the ActiveSheet and which sheet will be active.

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? For example, let us 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, we can use the code below if we do not want to delete a specific worksheet but all other worksheets.

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