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.
Place a command button in “test1” sheet.
Double-click on the button and open the VBA code editor or press Alt + F11.
Write the Delete command given below:
Sheets("test2").Delete
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.
Add a button in your sheet (as we did in the above example) and double-click to open the code editor.
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.
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.
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:
This will delete the second sheet from Workbook.
Note: Sheet index starts at 1.
For removing multiple sheets by array and sheet index number:
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.
