VBA Express : Excel - Delete blank sheets from the Workbook

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:

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

Test the code:

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

Sample File:

DelBlankSheets.zip 9.58KB

Approved by mdmackillop

This entry has been viewed 163 times.