Macro to Delete Blank Worksheets

This VBA macro deletes every worksheet that has nothing on it: no values, no formulas, no charts and no shapes.

Works inExcel 2010 and later, Windows and Mac
UndoNot possible. Save a copy first
Save asMacro-Enabled Workbook (.xlsm)

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

Delete all blank sheets

Sub DeleteBlankSheets()
    Dim i As Long, ws As Worksheet
    Application.DisplayAlerts = False
    For i = Worksheets.Count To 1 Step -1
        Set ws = Worksheets(i)
        If Application.WorksheetFunction.CountA(ws.Cells) = 0 And ws.Shapes.Count = 0 And Worksheets.Count > 1 Then ws.Delete
    Next i
    Application.DisplayAlerts = True
End Sub

CountA checks for any cell content, and Shapes.Count makes sure sheets holding only a chart or picture are kept. Worksheets.Count > 1 stops it trying to delete the last sheet.

Show how many were deleted

Sub DeleteBlankSheetsWithCount()
    Dim i As Long, n As Long, ws As Worksheet
    Application.DisplayAlerts = False
    For i = Worksheets.Count To 1 Step -1
        Set ws = Worksheets(i)
        If Application.WorksheetFunction.CountA(ws.Cells) = 0 And ws.Shapes.Count = 0 And Worksheets.Count > 1 Then ws.Delete: n = n + 1
    Next i
    Application.DisplayAlerts = True
    MsgBox n & " blank sheet(s) deleted."
End Sub

Things to know

  • A sheet with only formatting, such as coloured cells or borders, counts as blank and is deleted.
  • There’s no Undo. Save a copy first.
  • Hidden blank sheets are deleted too.

Related macros