VBA to loop through worksheets

[grapevine]

I am trying to write a macro to look at cell A2 in each sheet in a workbook, and if there is no data in this cell then to delete the worksheet. (so I am left with only the sheets with comprehensive data on). I am looking at cell A2 not A1 as there is likely to be residual date in row 1.

The code I have tried to write which is obviously not working and for which I need help is below. Any help as to what else i need to include or a better way of doing this task would be gratefully received. (The reason I am trying to automate this seemingly simple task is that it will be one small part of a much larger macro.)

Happy christmas and thanking you in advance.

Sub DeleteSheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If IsEmpty("A2") Then
ActiveSheet.Delete
End If
On Error Resume Next

Next ws
End Sub

 

[Jon von der Heyden]

Maybe:

Sub doit()

Application.DisplayAlerts = False

Dim i As Integer

x = Sheets.Count

For i = x To 1 Step -1
    If IsEmpty(Sheets(i).Range("A2")) Then
        Sheets(i).Delete
    End If
Next i

Application.DisplayAlerts = True

End Sub

 

[Andrew Poulsom]

Try:

Sub DeleteSheets()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Range("A2") = "" Then
            ws.Delete
        End If
    Next ws
End Sub

 

[Jon von der Heyden]

I think the fundamental error in your code is where you have:

ActiveSheet.Delete

should be:

ws.Delete

Also, Andrew's use of "" instead of IsEmpty is prolly a better method to use.

And turning off DisplayAlerts will make sure that you are not prompted with a warning each time a sheet is deleted.

Regards
Jon

 

[SteveO59L]

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Activate

If Range("a2") = "" Then
ActiveSheet.Delete

End If

Next ws

 

[grapevine]

Thank you all so much for your suggestions, I am now working and deleting sheets as required.

Happy christmas to you all

 

[Ekkehard]

Thank you very much Andrew.

Worked like a dream!