Excel Geeking: Removing All Empty Sheets using VBA

You ever need to go through a workbook you’re working on and delete a bunch of sheets that have nothing in them? Maybe you’re creating a new automated report that requires you to plunk down a large chunk of data in a new workbook, but when you create that new workbook, it creates it with “Sheet1” and “Sheete2” and “Sheet3”. Yes, I know, you can change how many new sheets are present when you create a new workbook, but if you need to make your code portable, cause maybe somebody else will be running the report, and maybe that person has a backup in case they’re out with meningitis or something, and that person has an admin do it–well, you can lose track of your users pretty quick.

And let’s be honest: a report, or some other workbook, that has a bunch of “Sheet2″s, “Sheet3″s, “Sheet4″s, and so forth, in it looks pretty amateur.

Wouldn’t it be nice to have a nice clean little function that let’s you go through and blow away all the sheets in a workbook that are empty of data without having to do that manually? And that aren’t just named “Sheet1”, “Sheet2”, etc.? It sure would. But how might you do that?

Wonder no more!

Below is some code I worked up for just such a task. It bases its actions on whether the UsedRange property is a single cell, that cell being the very first cell in a worksheet. If that’s all there is to the UsedRange, then we’ll take a quick look at the length of the data in that cell. If the length of the data is zero, well then that cell is empty, and that means the sheet is empty. There for we can blow it away.

As with most of my code, there is probably a better way of doing this. But this is what I landed on and it works beautifully (for me, at least). If you have a better method, chime in, I’d love to see how you tackled it.

Sub RemoveEmptySheets()
'   This deletes any sheet that has nothing in it.

    ' Variable declarations.
    Dim sh  As Worksheet

    ' Loop through all the sheets in the active workbook.
    For Each sh In ActiveWorkbook.Worksheets

        ' Perform two tests: checking if the used range is the very first cell,
        ' then checking to see if the lenght of hte cell contents is zero, meaning
        ' the cell is empty.
        If sh.UsedRange.Address = "$A$1" Then
            If Len(sh.Range("A1").Value) = 0 Then

                ' Delete the sheet.
                Application.DisplayAlerts = False
                sh.Delete
                Application.DisplayAlerts = True

            End If
        End If

    Next sh

End Sub