Delete sheets without confirmation prompts using VBA in Microsoft Excel

There could be a time when the vba macro / code needs to delete the sheets from the workbook.  It could be one sheet or several.  And excel gives a popup alert to confirm the sheet deletion.  If its one sheet, its manageable.  But if you have to delete several sheets, you will get one popup per sheet.  The popup is as shown below -
To click on Delete for each popup is cumbersome and delays the code completion.  You can avoid this by including Application.DisplayAlerts = False at the beginning of the code and Application.DisplayAlerts = True at the end of your code.  Your code will look like this –

If you have just one sheet to delete –
Option Explicit

Sub macro1()

Application.DisplayAlerts = False


Application.DisplayAlerts = True

End Sub
To copy the above code to your file,

  • Press Alt + F11 on the keyboard.
  • On the left hand side, you will see Microsoft Excel Objects.
  • Right click and select Insert.
  • Then click on Module.
  • Copy the code to the code window on the right.

See the code posted below in Module1.
If you have multiple sheets to delete and are using a loop, try this code  –
Option Explicit

Sub macro1()
Dim i As Long

Application.DisplayAlerts = False

For i = 1 to Worksheets.Count
If Worksheets(i).Name Like “Test*” Then Worksheets(i).Delete
Next i

Application.DisplayAlerts = True

End Sub
See the code posted in Module 1 below -
You can place these DisplayAlerts statements either at the beginning and end of your complete code if you have other alerts to disable or you can place them just above and below the delete code only. It depends on your code.

Arlette Aloysius

Arlette Aloysius has more than 10 years of experience working with excel. She started teaching excel and other microsoft office programs to government employees when she was in college as part of extra-curricular activities. In her 9 years of work experience, she has worked extensively on excel and automated several reports using vba. In her current role as administrator of, she provides simple as well as complicated vba solutions to users who need their reports to be automated. She is a Commerce graduate who specialized in Computer Applications where she was able to pick up the basics of the VB programming language. She then pursued to do her Post Graduate Diploma in International Business. Excel sheets are part of her daily life. She uses it even for small personal uses like keeping track of household expenses, investments, etc. By using excel as a tracker cum dashboard helps her to be up-to-date with all financial aspects in her day to day operations.

One thought on “Delete sheets without confirmation prompts using VBA in Microsoft Excel

  1. “If anyone uses ASAP utilities (personally I can’t live without it) there is a list all range names function that lists all range names on a new worksheet.

    Even after deleting all range names in INSERT/DEFINE/DELETE menu it still lists them all. Is this a problem with ASAP or are they still really there?”

Leave a Reply

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

3 + eight =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>