Delete 1, 2 or More Sheets in VBA by Name, Number etc.

How to delete Excel sheet in VBA

In this tutorial, we will show you how to delete one or more sheets by using VBA Sheet.Delete method.

By using the Delete method, you may remove:

Single sheet
Multiple sheets
All sheets except one
Sheets by name
Sheet by index

See the examples of removing sheets in the section below.

An example of removing a single sheet by name in VBA

We will use the following Workbook that contains four worksheets as shown below:

For deleting a single sheet, follow these steps.

Step 1:

Place a command button in “test1” sheet.

Step 2:

Double-click on the button and open the VBA code editor or press Alt + F11.

Step 3:

Write the Delete command given below:

Sheets("test2").Delete

Step 4:

Excel will prompt a message box.

Press the “Delete” button to permanently delete that sheet.

Remaining sheets in our sample Workbook:

Deleting two sheets example

While deleting a single is quite simple, if you want to delete two or more sheets, it needs some workaround.

The simplest way is to use the Array with Sheets object in VBA.
Inside the array, you may refer sheets that you want to remove and then just use the Delete method.
Suppose, we want to delete two sheets (test2, test4) from our sample Workbook.
Follow these steps to remove two sheets as the button is clicked.

Step 1:

Add a button in your sheet (as we did in the above example) and double-click to open the code editor.

Step 2:

Write the following line of code with the Delete method in the click event of the button:

Sheets(Array("test2", "test4")).Delete

Save the VBA code.

Step 3:

Click the button and Excel should prompt you to delete or not. Pressing “Delete” in our case will delete the sheets as shown below:

Similarly, you may delete three or more Worksheets by name. Just add the name:

Sheets(Array("test2", "test3", "test4")).Delete

This will delete three sheets with one click of the button.

Note: Ensure to click on the button after unselecting “Design Mode” in the DEVELOPER Tab.

Disable the warning prompt in Excel

If you want to remove the Excel warning message box before the deletion of the sheets, you may do this easily.

You may set the False value for Application.DisplayAlerts and it’s done:

The following code will delete the “test4” sheet without a warning message:

    'Disable Excel Alert message

    Application.DisplayAlerts = False

        Sheets(Array("sheet3")).Delete

    'Enable Alerts back

    Application.DisplayAlerts = True

Delete a single sheet by index number

One or more sheets can also be deleted by using the sheet index number.

Execute the following code in a button-click event.

For removing one sheet by index number:

Sheets(2).Delete

This will delete the second sheet from Workbook.

Note: Sheet index starts at 1.

For removing multiple sheets by array and sheet index number:

Sheets(Array(2, 4, 5)).Delete

This will remove the second, fourth, and fifth sheets (if exist).

Deleting all sheets (except one) example

If you want to delete all sheets except one in the Workbook then you may use the for Each or For loop to achieve that.

See an example below where we will use a for loop and delete all sheets except for the first one.

For the example, we have many sheets in the Workbook:

VBA code:

    Dim x, y As Long

    Application.DisplayAlerts = False

    'Getting toal count of sheets in the Workbook
    x = Worksheets.Count

    'Loop will execute highest to lowest sheet

    For y = x To 2 Step -1

        Sheets(y).Delete

    Next y

    Application.DisplayAlerts = True

Place this code in a button or a function and then call it.