Delete blank sheets from the Workbook

Ease of Use


Version tested with


Submitted by:



All sheets containing no information will be deleted 


Prior to emailing or archiving a file, you may wish to delete all of the blank worksheets. This code checks each sheet, counts all the cells containing data, and if none is found, deletes the sheet. 


instructions for use


Option Explicit Sub DeleteBlankSheets() Dim sh As Variant Application.ScreenUpdating = False Application.DisplayAlerts = False On Error GoTo Exits: For Each sh In Sheets If Not IsChart(sh) Then If Application.WorksheetFunction.CountA(sh.Cells) = 0 Then sh.Delete End If Next sh Exits: Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub Public Function IsChart(sh) As Boolean Dim tmpChart As Chart On Error Resume Next Set tmpChart = Charts(sh.Name) IsChart = IIf(tmpChart Is Nothing, False, True) End Function

How to use:

  1. Copy above code.
  2. In Excel press Alt + F11 to enter the VBE.
  3. Press Ctrl + R to show the Project Explorer.
  4. Right-click desired file on left (in bold).
  5. Choose Insert -> Module.
  6. Paste code into the right pane.
  7. Press Alt + Q to close the VBE.
  8. Save workbook before any other changes.

Test the code:

  1. From Excel, press Alt + F8 to open the macro dialog box.
  2. Select DeleteBlankSheets
  3. Click Run.

Sample File:

DelBlankSheets.zip 9.58KB 

Approved by mdmackillop

This entry has been viewed 160 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express