Delete sheets without confirmation prompts using VBA in Microsoft Excel

There are times when we have create or add sheet & later on found of no use hence need to delete quickly from the workbook. This article focus on saving time & provide code for removing sheets without any prompt message box using vba macro / code. 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.

If you have just one sheet to delete then your code will look like this –


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 macro2()
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.


image 29


If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at

Users are saying about us...

  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?”

      • Hi Teressa,

        Greetings from Excel Tip & Excel Forum!

        Thanks for visiting us and If you really want to learn VBA in depth, keep visiting us regularly as we at Excel Tip, try to bring new stuff every day on MS-Excel & VBA.

        In case you have any complicated or simply query, you can visit us at Excel Forum and can ask to our experts.

        Team Excel Tip & Excel Forum

  2. I am trying to delete multiple sheets with the ending _2015. I would like to delete them all at once. The problem is there are about 120 sheets and they either have the ending ###_2015 and others are the ###_2015## (these are the ones I want to keep). I am either deleting everything or the warning appears and I delete them one at a time. Thanks in advance :)

  3. Hi Tracy,

    Please check the following code:

    Sub DeleteSheets()
    Dim Counter As Integer
    Application.DisplayAlerts = False
    For Counter = ThisWorkbook.Worksheets.Count To 1 Step -1
    If Right(Worksheets(Counter).Name, 5) = "_2015" Then Sheets(Counter).Delete
    Next Counter
    Application.DisplayAlerts = True
    End Sub


  4. Thanks for your quick response. I tried that, but nothing happened. I saved the macro and rebooted hoping that would make a difference. Any other suggestions? Thanks again :)

  5. I got it! There were 2 spaces behind the 2015, so I changed it to 7 and added to 2 spaces. Thanks again for your assistance. Will have another situation soon. MANY THANKS! :)

  6. Hi, I told you I would be back. Here is my new situation. I need to move all the data from the multiple tabs onto a new worksheet. All of the worksheets have the same headers and # of columns of data, so I would need on the new worksheet to have them all line up underneath each other so no rows are skipped. On the new worksheet, I would need column A to be titled “Facility #” and the facility # is on each tab name. For example, the tabs are named the actual facility #, which is anywhere between 3 – 5 digits, _2015 month #. (IE – 79810_201506). Thanks again for your assistance :)

    • Hi Tracy,

      Thanks once again for writing us.

      We would request you to login on Excel Forum for your any difficulty or complicated MS-Excel/VBA query. Our experts will surely assist you.

      Keep visiting us!

      Team Excel Tip & Excel Forum

  7. I tried to click on the Excel Forum link, but my company laptop has the site blocked. It says because it may pose a security threat? Any other way I can access? Thanks again

    • Hi Tracy,

      Your company might has set some sort of firewall and because of that you might not be able to access the site. Could you try using some other internet connection may be at home or so.

      Do let us know if it works then.

      Best regards,
      Team Excel Tip & Excel Forum

  8. Are you sure that your code to delete multiple sheets work?
    For me it works by starting at the end (last sheet)

    Sub delWorksheet()
    ‘ delete all Worksheet without “Something”

    Dim n As Long

    Application.DisplayAlerts = False

    For n = Worksheets.Count To 1 Step -1
    If Worksheets(n).Name Like “Something” Then
    ‘do nothing
    End If
    Next n

    Application.DisplayAlerts = True

    End Sub

Leave a Reply

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

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>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube