Table of Contents
VBA can be used to delete sheets without prompting or warning. This can be done by using the ‘Delete’ method on the Worksheet Object, which will delete the sheet without prompting or warning. The code required would look something like this: ActiveWorkbook.Worksheets(“Sheet1”).Delete. This would delete the sheet named “Sheet1” without any prompting. This code can be repeated for each sheet that needs to be deleted. Additionally, the ‘Application.DisplayAlerts’ can be set to ‘False’ to suppress any pop-up messages that appear when deleting a sheet.
When using the Delete method in VBA to delete a specific sheet in a workbook, Excel will issue a prompt asking if you’re sure you want to delete the sheet.
However, you can use the following syntax in VBA to delete a sheet without any prompt or warning box:
Sub DeleteSheets()
'turn off display alerts
Application.DisplayAlerts = False
'delete Sheet1
Sheets("Sheet1").Delete
'turn back on display alerts
Application.DisplayAlerts = True
End Sub
This particular macro deletes the sheet called Sheet1 without any prompt or warning box.
The line Application.DisplayAlerts=False tells VBA to turn off any display alerts in Excel.
We then use the Delete method to delete a specific sheet.
We then use Application.DisplayAlerts=True to turn back on display alerts.
The following example shows how to use this syntax in practice.
Example: Use VBA to Delete Sheet Without Prompt or Warning
Suppose we have the following Excel workbook that contains three sheets:
Now suppose that we would like to create a macro to delete the sheet called Sheet1.
Suppose we create the following macro:
Sub DeleteSheets()
'delete Sheet1
Sheets("Sheet1").Delete
End Sub
When we run this macro, we will receive a message that asks if we’re sure we want to delete this sheet:
However, we can create the following macro to delete Sheet1 without any prompt:
Sub DeleteSheets()
'turn off display alerts
Application.DisplayAlerts = False
'delete Sheet1
Sheets("Sheet1").Delete
'turn back on display alerts
Application.DisplayAlerts = True
End Sub
When we run this macro, the sheet called Sheet1 is automatically deleted and no prompt appears:
Notice that Sheet1 has been deleted while the other two sheets have remained untouched.