Hello Excellers, today let’s look at how to delete all worksheets in an Excel workbook where a certain range of cells is empty. I use this piece of code every week on a report generated automatically which can contain sheets that that are not required (up to 20 of them). It takes a lot of time to look into each sheet and check if there is any relevant data in the sheets, and then manually delete them, this small piece of code saves me time each and every week, so I wanted to share it with you.

WARNING!!….Use this code with care as once the sheets are deleted they are gone!.

If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

If you want to see all of the blog posts in the Macro Mondays Series or the example worksheets you can do so by clicking on the links below.

How To Excel At Excel – Macro Mondays Blog Posts.

[wpdm_package id=’10338′]

What Does The Macro Do?

This Macro will loop through all of the worksheets in your active workbook, and if the range you specify in the Macro is empty then every sheet that matches these criteria will be deleted.

How Does It Work?

FIRST. Open Visual Basic – by hitting ALT +F11 or Developer Tab – Right Click on personal.xbl in the Project Window. I have stored this macro in my Personal Macro Workbook, so it is available to use whenever I start Excel and can apply it to any Excel workbook I choose to.

Step 2. We need to declare a variable. This ensures that Excel creates a memory container for it.

Dim MySheets As Worksheet

Step 3.
This part of our code turns off any warnings displayed as you are about to delete worksheets. This is optional but does save the user having to confirm it is Ok to delete the worksheets.

Step 4. Excel will now loop through each of the worksheets in the active workbook.

Step 5. If the worksheet range (in this case A1) then Excel deletes the worksheet, and the loop begins again, until all worksheets have been assessed.

Step 6. This last step turns back on Excel alerts that were turned off in Step 3, and the routine ends.

Want The Code?

Sub DeleteEmptyWorksheets()
Dim MySheets As Worksheet

Application.DisplayAlerts = False
For Each MySheets In ActiveWorkbook.Worksheets
If MySheets.Range(“A1”) = “” Then
MySheets.Delete
End If
Next
Application.DisplayAlerts = True
End Sub