BUY EXCEL BOOKS ONLINE: 1. VBA & Macros      2. VBA for Modelers      3. Excel 2013 VBA and Macros     
4. Excel VBA for Dummies      5. Excel with VBA & .NET      6. Mastering VBA      7. Excel 2013 Programming

Delete/Remove Blank/Empty Worksheets


Sub DeleteBlankSheets()
    Dim sh As Variant
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
On Error GoTo Exits:
    For Each sh In Sheets
'IsChart checks, if sh is a Chart Object or some other object.
        If Not IsChart(sh) Then

'CountA checks if there is any data in cells of the sheet
            If Application.WorksheetFunction.CountA(sh.Cells) = 0 Then sh.Delete
        End If
    Next sh
    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

Bojja said...

Really Great Stuff......Appreciate your help...

evelynm said...

Wonderful scripts! But for the VB scripts, i must say that not all users are programmers and will be confused about how to write it. Actually getting an application available to delete all the blank workbooks in one click might be a better choice, right? Kutools for Excel is such an add-ins tools for Microsoft Excel, which provides option to help elete all the blank workbooks. No need to worry about the repetitive operations again. Just take a look and try it.