Excel

Delete blank sheets from the Workbook

Ease of Use

Easy

Version tested with

2003 

Submitted by:

mdmackillop

Description:

All sheets containing no information will be deleted 

Discussion:

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. 

Code:

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 153 times.

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