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.
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.
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.”
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.
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.
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
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.
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.
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.
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.
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.
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.
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”.
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 –