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.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
VBA: How to Count Number of Sheets in Workbook
VBA: How to Extract Data from Another Workbook
VBA: How to Insert Multiple Rows
