Delete all worksheets in a workbook that have a blank value in cell A2

[VBAProIWish]

Hello All,

I have this code below that looks to the active worksheet and then deletes the worksheet if cell A2 is blank but it is only for the active worksheet and I want the code below to look at every sheet in the workbook and do the same thing. The workbook is a dynamic one so the names and number of worksheets can be different every time I run the code.

In other words, I need code that looks at cell A2 in every worksheet and deletes whichever worksheets have a blank value.

'Sub delete_WSS_with_no_data()
Application.DisplayAlerts = False
If IsEmpty(Range("A2").Value) = True Then
    ActiveSheet.delete
End If
Application.DisplayAlerts = True
'End Sub

Thanks much!

 

[Joe4]

Try this:

Sub MyDeleteSheets()

    Dim ws As Worksheet
   
    Application.DisplayAlerts = False
   
    For Each ws In Worksheets
        If IsEmpty(ws.Range("A2")) Then ws.Delete
    Next ws

    Application.DisplayAlerts = True
   
    MsgBox "Macro complete!"

End Sub

Note: Make sure you have at least one sheet that has something in cell A2. I think you will run into problems if you try to delete all sheets in a workbook!

 

[jolivanes]

If A2 in all sheets is empty, you'll need safeguard because you can't delete all sheets. Like mentioned above.

Sub Try_So()
Dim i As Long
    For i = ThisWorkbook.Worksheets.Count To 1 Step -1
        If ThisWorkbook.Sheets.Count > 1 Then
            If Len(Worksheets(i).Cells(2, 1)) = 0 Then
                Application.DisplayAlerts = False
                    Worksheets(i).Delete
                Application.DisplayAlerts = True
            End If
        End If
    Next i
End Sub

 

[VBAProIWish]

The first code worked! Thank you!

Jolivanes, your code gave me a "Subscript Out of Range" error on this line here...

  If Len(Worksheets(i).Cells(2, 1)) = 0 Then

but I will never have a workbook where all worksheets have a blank value in cell A2.

I can see how that may be helpful in the future though.

Thank you!

 

[Joe4]

You are welcome.
Glad we were able to help!