VBA: How to Delete Sheets Without Prompt or Warning


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

Featured Posts

Leave a Reply

Your email address will not be published. Required fields are marked *