VBA to delete row on all worksheets meeting multiple criteria

[Deirdre]

I have a workbook which contains 7 worksheets of various row length. I have a macro that already performs various tasks which works fine but now I would like to add to it by searching every worksheet and deleting any row that contains a 0 or the text NULL in column A.
After a search, I found the following but it only works on the active worksheet.

Sub test()

   Dim i As Long
    Dim sht As Worksheet
    
    For Each sht In ActiveWorkbook.Sheets
        For i = sht.UsedRange.Rows.Count To 2 Step -1
            If Cells(i, 1).Value = "NULL" OR Cells(i, 1).Value = 0 Then Cells(i, 1).EntireRow.Delete
        Next i
    Next sht
End Sub

 

[Skyybot]

Try this. Small change to your code.

Sub test()

   Dim i As Long, x As Integer
    Dim sht As Worksheet
    
    For x = 1 To ThisWorkbook.Worksheets.Count
    Set sht = ThisWorkbook.Worksheets(x)
        For i = sht.UsedRange.Rows.Count To 2 Step -1
            If Cells(i, 1).Value = "NULL" Or Cells(i, 1).Value = 0 Then Cells(i, 1).EntireRow.Delete
        Next i
    Next x
End Sub

 

[Deirdre]

Try this. Small change to your code.

Sub test()

   Dim i As Long, x As Integer
    Dim sht As Worksheet
   
    For x = 1 To ThisWorkbook.Worksheets.Count
    Set sht = ThisWorkbook.Worksheets(x)
        For i = sht.UsedRange.Rows.Count To 2 Step -1
            If Cells(i, 1).Value = "NULL" Or Cells(i, 1).Value = 0 Then Cells(i, 1).EntireRow.Delete
        Next i
    Next x
End Sub

Thank you for the reply. It did not work - not even on the active sheet.

 

[Skyybot]

Thank you for the reply. It did not work - not even on the active sheet.

Are you getting an error?

 

[Deirdre]

Are you getting an error?

No.

 

[Skyybot]

No.

Where have you placed the code?

 

[Deirdre]

I replaced the code I have above in the same module. I did not embed it in the greater macro as I wanted to see it run on its own first with my workbook.

 

[Skyybot]

I replaced the code I have above in the same module. I did not embed it in the greater macro as I wanted to see it run on its own first with my workbook.
View attachment 86761

Ah. I wrote it to work in the Workbook with the data in it. This small change should work. Replace "YourWorkbook" with Workbook Name.

Sub test()

   Dim i As Long, x As Integer
    Dim sht As Worksheet
    
    For x = 1 To Workbooks("YourWorkbook").Worksheets.Count
    Set sht = ThisWorkbook.Worksheets(x)
        For i = sht.UsedRange.Rows.Count To 2 Step -1
            If Cells(i, 1).Value = "NULL" Or Cells(i, 1).Value = 0 Then Cells(i, 1).EntireRow.Delete
        Next i
    Next x
End Sub

 

[Deirdre]

Ah. I wrote it to work in the Workbook with the data in it. This small change should work. Replace "YourWorkbook" with Workbook Name.

Sub test()

   Dim i As Long, x As Integer
    Dim sht As Worksheet
   
    For x = 1 To Workbooks("YourWorkbook").Worksheets.Count
    Set sht = ThisWorkbook.Worksheets(x)
        For i = sht.UsedRange.Rows.Count To 2 Step -1
            If Cells(i, 1).Value = "NULL" Or Cells(i, 1).Value = 0 Then Cells(i, 1).EntireRow.Delete
        Next i
    Next x
End Sub

Thank you again for your time. I am getting an error. I did try to replace ThisWorkbook with ActiveWorkbook and also the name of the workbook in " " with no luck once I received the error.

 

[Skyybot]

Thank you again for your time. I am getting an error. I did try to replace ThisWorkbook with ActiveWorkbook and also the name of the workbook in " " with no luck once I received the error.
View attachment 86762
View attachment 86763

Jeez. I'm sorry. Made the change to one line but not the other. Try replacing the highlighted line with the following.

Set sht = Workbooks("YourWorkbookName").Worksheets(x)