Delete sheets with macro

[KreshBell]

Hi all,
I have an excel template that is filled with data from CAD system. The template has always the same number of sheets, but sometimes CAD fills all sheets, sometimes only some. If it does not fill the sheet, only the header remains in that sheet. I call all sheets that only have a header "empty".


After CAD extracts the data into Excel, I would like to delete all sheets that only have a header. Is it possible to make a macro for that?

 

[rollis13]

I'm assuming that the sheets with the data all have cell A2 populated, and also that when you start the macro the active workbook is this workbook with the data.
Try this macro of mine:

Option Explicit
Sub DeleteSheetsWithNoData()
    Dim sht, cnt As Integer
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For Each sht In Worksheets                    'loop all sheets in workbook
        If sht.Range("A2") = "" Then
            sht.Delete                            'if cell A2 is empty delete sheet
            cnt = cnt + 1
        End If
    Next sht
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    If cnt Then
        MsgBox "Done! deleted " & cnt & " sheets"
    Else
        MsgBox "No sheets to delete!"
    End If
End Sub

 

[KreshBell]

really nice, thanks!!!

 

[rollis13]

Thanks for the positive feedback, glad having been of some help.

 

[Marc L]

Hi ! @rollis13 : for the fun a demonstration deleting worksheets at once, to better paste to ThisWorkbook module :​

Sub Demo1()
       Const C = "¤"
         Dim Ws As Worksheet, S$
    For Each Ws In Worksheets
        If IsEmpty(Ws.[A2]) Then S = S & String(-(S > ""), C) & Ws.Name
    Next
    If S > "" Then
        Application.DisplayAlerts = False
        Worksheets(Split(S, C)).Delete
        Application.DisplayAlerts = True
    End If
End Sub

 

[rollis13]

@Marc L, I 'love' this one: String(-(S > ""), C)
It went beyond my ability but now it's in my piggy bank.