Delete Empty Worksheets

[MattMax]

Hello,

I would like to create a macro that searches a workbook to determine how many worksheets contain data. It will delete the empty worksheets and record in an array, the position of the active worksheets.

So far I'm getting stuck on the delete function
"A workbook must contain at least one visible worksheet"

Any help would be great. Thanks!


Sub Find_worksheets()

Dim shtNext As Worksheet
Dim WorkSheetArray(0 To 9)

worksheetnumber = ActiveWorkbook.Worksheets.Count
MsgBox "There are " & worksheetnumber & " Worksheets"

For Each shtNext In ActiveWorkbook.Worksheets
With shtNext.Range("AD2")
If .Value > 0 Then
WorkSheetArray(shtNext) = shtNext
Else
ActiveWorkbook.Worksheets.Delete
End If
End With
Next shtNext

End Sub

 

[BrianB]

The error message is explicit. We must have at least 1 sheet in a workbook.
If all sheets are empty why keep the workbook ?

Here is some code.

'=============================================================================
'- DELETE WORKSHEETS WITH EMPTY CELL D2
'=============================================================================
Sub test()
    Application.DisplayAlerts = False       ' to stop delete messages
    On Error Resume Next                    ' to trap error
    '-------------------------------------------------------------------------
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Range("D2").Value = "" Then
            ws.Delete
            '-----------------------------------------------------------------
            '- TRAP DELETE ERROR
            If Err.Number <> 0 Then
                Err.Clear
                MsgBox ("Cannot delete sheet '" & ws.Name & " '")
                Exit Sub
            End If
            '----------------------------------------------------------------
        End If
    Next
    '========================================================================
    '- NB. MAKE SURE THAT THIS LINE RUNS OR WILL GET NO FURTHER MESSAGES
    Application.DisplayAlerts = True
    '========================================================================
End Sub

====================================================
NB. DisplayAlerts must be used with extreme care. It stops all messages (in this case the prompt to confirm deletion).

Must run the line of code to reset to true.

    Application.DisplayAlerts = True

'=======================================================

 

[MattMax]

Thanks! That worked great.