how to delete sheets in excel

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

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.

You are free to use this image on your website, templates etc, Please provide us with an attribution link Article Link to be Hyperlinked
For eg:
Source: VBA Delete Sheet (wallstreetmojo.com)

How to Delete Excel Sheets using VBA Code?

You can download this VBA Delete Sheet Excel Template here – VBA Delete Sheet Excel Template

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 VBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task. read more .

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