VBA Delete Sheet

Excel VBA Delete Sheet

By this time, we all know how to Unprotect Sheet any sheet. VBA Delete sheet has the same way of writing code as an Unprotect sheet. VBA Delete Sheet, as the name says is used for deleting the selected worksheet automatically. For using the VBA Delete function to delete any sheet, first, we need to identify which sheet we want to delete. VBA Delete function works when we call the name of the sheet or sequence of that sheet and then use Delete function after that. Below is the syntax of the VBA Delete Sheet.

The above-shown syntax will be used in many different ways in upcoming examples.

How to Delete Sheet in Excel VBA?

We will learn how to Delete Sheet in Excel by using the VBA Code.

VBA Delete Sheet – Example #1

In this example, we will see a simple way of deleting any worksheet. For this, follow the below steps:

Step 1: Open a Module from the Insert menu tab.

Step 2: Now write the subcategory of the VBA Delete Sheet. We can use any other name to define the code.

Code:

Sub VBA_DeleteSheet()

End Sub

Step 3: Use Worksheets object and select the sheet which we want to delete. Here that sheet is named as Sheet1.

Code:

Sub VBA_DeleteSheet()

Worksheets("Sheet1").

End Sub

Step 4: Now use Delete function after the name of the selected Sheet.

Code:

Sub VBA_DeleteSheet()

Worksheets("Sheet1").Delete

End Sub

Step 5: Run the code by pressing the F5 key or by clicking on the Play Button.

We will see the system will prompt us with an Alert that excel will permanently delete the sheet. To proceed further click on Delete.

Step 6: We will see the sheet “Sheet1” is now deleted.

Step 7: Suppose, we try to delete a sheet that does not exist or if we end up putting an incorrect name. Below I tried to enter the name of the sheet which is not there in the excel file as shown below.

Code:

Sub VBA_DeleteSheet()

Worksheets("Sheet2").Delete

End Sub

Step 8: Now run the code. We will see, VBA gives us the error “Subscript out of Range” which means we have selected a sheet that does not exist.

VBA Delete Sheet – Example #2

There is another way to implement VBA Delete Sheet. This is not as small code as the method which we have seen in Example-1. But also an easy way to implement. For this, follow the below steps:

Step 1: Write the subprocedure of the VBA Delete sheet as shown below.

Code:

Sub VBA_DeleteSheet2()

End Sub

Step 2: Now select a variable for Worksheet with any name. Here we have chosen ExSheet as shown below.

Code:

Sub VBA_DeleteSheet2()

Dim ExSheet As Worksheet

End Sub

Step 3: Now Set the defined variable with the Worksheets function and there put the name of the sheet which we want to Delete. Here, again the sheet name is “Sheet1”.

Code:

Sub VBA_DeleteSheet2()

Dim ExSheet As Worksheet

Set ExSheet = Worksheets("Sheet1")

End Sub

Step 4: Now assign the defined variable with Delete function as shown below.

Code:

Sub VBA_DeleteSheet2()

Dim ExSheet As Worksheet

Set ExSheet = Worksheets("Sheet1")

ExSheet.Delete

End Sub

Step 5: Now run the code by pressing the F5 key or by clicking on the Play button.

We will again get a prompt message as we still want to proceed to delete the selected Sheet. Click on Delete to proceed.

VBA Delete Sheet – Example #3

In this example, we will see, how to delete a sheet that is currently active. For this, follow the below steps.

Step 1: Write the subprocedure of the VBA Delete Sheet as shown below.

Code:

Sub VBA_DeleteSheet3()

End Sub

Step 2: Use the same line of code for declaring the variable as Worksheet as shown below which we have used in the above examples.

Code:

Sub VBA_DeleteSheet3()

Dim ExSheet As Worksheet

End Sub

Step 3: Now open a For Next loop as shown below.

Code:

Sub VBA_DeleteSheet3()

Dim ExSheet As Worksheet

For 

Next ExSheet

End Sub

Step 4: Now we will select the active current worksheet from all the worksheets available in Excel Workbook.

Code:

Sub VBA_DeleteSheet3()

Dim ExSheet As Worksheet

For Each ExSheet In ActiveWorkbook.Worksheets

Next ExSheet

End Sub

Step 5: Once the currently opened worksheet is selected, we will use Delete function with variable ExSheet to delete that.

Code:

Sub VBA_DeleteSheet3()

Dim ExSheet As Worksheet

For Each ExSheet In ActiveWorkbook.Worksheets

ExSheet.Delete

Next ExSheet

End Sub

Step 6: Now compile the code and run it if there is no error found. This will delete the active worksheet from the opened workbook which is selected.

VBA Delete Sheet – Example #4

The code which we have seen in example-3 can also be seen in one more way. We can use If End If Loop along with the For Next loop.

Step 1: Let’s just consider the same code which we have seen in the above example-3.

Code:

Sub VBA_DeleteSheet4()

Dim ExSheet As Worksheet
For Each ExSheet In ActiveWorkbook.Worksheets
ExSheet.Delete
Next ExSheet

End Sub

Step 2: This code deletes the sheet which is currently active. What if we improve this condition by selecting the sheet which is currently active and with the exact same name? Yes, for this we need to open If End If loop where we will write the condition for this just after For loop.

Code:

Sub VBA_DeleteSheet4()

Dim ExSheet As Worksheet
For Each ExSheet In ActiveWorkbook.Worksheets
If
ExSheet.Delete
End If
Next ExSheet

End Sub

Step 3: Now write the condition in If loop as if the name of the sheet under variable ExSheet is equal and exact to “Sheet1” (Or any other sheet name), then Delete that sheet using variable ExSheet.

Code:

Sub VBA_DeleteSheet4()

Dim ExSheet As Worksheet
For Each ExSheet In ActiveWorkbook.Worksheets
If ExSheet.Name <> "Sheet1" Then
ExSheet.Delete
End If
Next ExSheet

End Sub

Step 4: We can compile the code here as there are many steps are involved. Then run the code after that.

Again we will get the prompt message where we still want to Delete that sheet or not. By clicking on Delete, it will delete the sheet which is selected.

Pros of Excel VBA Delete Sheet

• This is quite useful when we have a huge set of data in the different sheets which we need to delete quite often once the task is done.
• Code shown in example-1 is the easiest code to apply.
• We can even select more than one sheet which we want to delete.

Things to Remember

• We can choose the sequence of the sheet as well instead of the Sheet name which we want to delete.
• The reason behind getting the alert before deleting the sheet is that Excel prompts us to check if there is any data which we don’t want to lose.
• Once we are done with coding, save the excel file in Macro enable excel format to avoid losing the written code.

Recommended Articles

This is a guide to VBA Delete Sheet. Here we discuss how to Delete Sheet in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA Collection
2. VBA IF Statements
3. VBA While Loop
4. VBA Environ