Excel Automation: Identify and Remove Blank Worksheets

Question

I want to delete a worksheet if it does not contain any data / diagrams / images / drawing objects / hyperlinks or any other embedded objects.

I found a solution to detect and remove blank sheets if there is no data in the cells using the following code: -

if ($ Worksheet_Function-> CountA ($ sheet → {Cells}) == 0) {$ Sheet-> Delete; }

but also deletes the sheet if there are diagrams or non-text objects.

Is there a way to identify and delete sheets if it is completely empty?

Answer 1

will delete the sheet if there is only formatting, but that should do what you ask

Sub chksheet()
 Dim wks As Worksheet
 Application.DisplayAlerts = False

 For Each wks In ActiveWorkbook.Worksheets

 If WorksheetFunction.CountA(Cells) = 0 And wks.DrawingObjects.Count = 0 Then
  wks.Delete
  Else
   MsgBox ("has stuff") 'or do nothing here and skip this sheet
  End If
 Next wks

 Set wks = Nothing
  Application.DisplayAlerts = True
 End Sub

Answer 2

,

Option Explicit

Sub test()
Dim WS As Worksheet

For Each WS In ThisWorkbook.Worksheets
    With WS
        'default usedrange = 1 so check cell A1 is also empty
        If .UsedRange.Count = 1 And IsEmpty(.Cells(1, 1).Value) _
            And .UsedRange.Column = 1 _
            And .UsedRange.Row = 1 _
            And .Comments.Count = 0 _
            And .Shapes.Count = 0 _
            And .Hyperlinks.Count = 0 _
            And .ListObjects.Count = 0 _
            And .OLEObjects.Count = 0 _
            And .Names.Count = 0 _
            And .QueryTables.Count = 0 _
            And .SmartTags.Count = 0 Then

            MsgBox ("BLANK")
            'WS.delete
        Else
            MsgBox ("NOT BLANK")
        End If

        End With
Next WS
End Sub