Introduction
Delete Sheet with Specific Name
Delete Sheet if it Exists
Delete Sheets Without Warning Prompt
Loop Through Sheets and Delete Without Prompting if Sheet Exists
Final Thoughts

Introduction

In this tutorial, I’ll show you 4 ways to delete worksheets using VBA, including examples of how to delete sheets without the annoying Excel warning prompt.


Delete Sheet with Specific Name

Example 1

Sub VBA_Delete_Sheet()
     Sheets("Sheet1").Delete
End Sub

This is the simplest way to delete a worksheet. However, it’s not very robust. For example, if you try to delete a worksheet that doesn’t exist using this macro, you’ll get an error like this one:

Run-Time Error 9

You can prevent the “subscript out of range” error by testing if the worksheet exists before trying to delete it. This is exactly what we’ll do in our next example. I’ve developed an [introduction to the Worksheet Object tutorial] to help you out if you’re feeling a bit uncomfortable using the Sheets object.


Delete Sheet if it Exists

Example 2

Sub VBA_Delete_Sheet()
For Each Sheet In ActiveWorkbook.Worksheets
     If Sheet.Name = "Sheet1" Then
          Sheet.Delete
     End If
Next Sheet
End Sub

This macro loops through each sheet in your active workbook. If it finds a sheet named Sheet1, it will delete it. If it doesn’t find the sheet, it will exit cleanly without deleting anything and, more importantly, without giving you an error.

If you use either of the two previous macros to delete a worksheet that isn’t empty, you’ll probably get a warning prompt like this one:

Content exists on sheet prompt

This happens every time you try to delete a worksheet with content on it. It’s unbelievably annoying if you’re trying to delete multiple sheets in one macro. Scroll down to the next VBA example for a solution to this problem.


Delete Sheets Without Warning Prompt

Example 3

Sub VBA_Delete_Sheet()
Application.DisplayAlerts = False
Worksheets("Sheet1").Delete
Application.DisplayAlerts = True
End Sub

This macro suppresses the Excel alerts prior to deleting your sheet and re-activates the alerts immediately after deleting your sheet. By doing this, you no longer get pestered by the message saying “Data may exist in the sheet(s) selected for deletion.”

That’s right - no more warning prompt! Your sheet just automatically deletes. No questions asked.


Loop Through Sheets and Delete Without Prompting if Sheet Exists

Example 4

Sub VBA_Delete_Sheet()
For Each Sheet In ActiveWorkbook.Worksheets
    If Sheet.Name = "Sheet1" Then
        Application.DisplayAlerts = False
        Worksheets("Sheet1").Delete
        Application.DisplayAlerts = True
    End If
Next Sheet
End Sub

This final macro combines everything you’ve learned in this tutorial. It loops through all the sheets in your Excel workbook and once it finds the sheet you want to delete, it will delete it without stopping to show you the warning prompt.

I’m rather fond of this macro because it shows the beauty of VBA. Why do I like it? I’ve said it once, and I’ll happily say it again. This macro shows how you can (and should!) combine each new skill you learn into wonderful and clean macros.


Final Thoughts

I hope you found these tutorials helpful. You now know 4 ways to delete worksheets using VBA and you’ve learned how you can combine these examples into powerful macros. Powerful macros that bypass the “content exists” warning prompt and that will not generate an error if the sheet you’re trying to delete doesn’t exist.

Please subscribe to my email list for more great VBA tips designed to help you Master VBA. Share this article with people you know, start a conversation by leaving a comment below and follow me on Google+!

I’m also on Twitter!

Come back often to see more great VBA ideas.

Discover how this Nuclear Engineer Mastered Excel VBA

And why you should, too


Your time is valuable. It's time to become a VBA expert.

About Ryan Wells


Ryan Wells

Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.