Delete Row on Multiple Criteria not Working

[Jl23]

I'm trying do a simple code to delete rows if a condition in column B & column E are met, both need to be true to have the row deleted. Column B needs to have any text and Column E has to be blank. I've tried other versions of code I've found in examples trying to troubleshoot but I can't get anything to work with 'And'. If I switch out the And to Or I get things to work but then it is deleting rows I don't want deleted. Can anyone explain what might be causing it not to work? In image attached I would want the highlighted rows to be deleted only.

Sub delete_rows()

Dim row_count As Long
Dim i As Long
Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets("Billing")

ws.Activate
row_count = ws.Cells(Rows.Count, "A").End(xlUp).Row

i = 5
Do While i <= row_count

If Cells(i, 2) = "*" _
And Cells(i, 5) = "" Then

Rows(i).EntireRow.Delete
i = i - 1
row_count = row_count - 1
End If

i = i + 1

Loop

End Sub

 

[Joe4]

When deleting rows, it is best to cycle through the range backwards so you are not changing the size of the range you have yet to analyze (by deleting rows and shifting up).
Try this:

Sub Delete_Rows()

    Dim lr As Long
    Dim r As Long
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("Billing")
    ws.Activate
    
    Application.ScreenUpdating = False
    
'   Find last row with data in column B
    lr = Cells(Rows.Count, "B").End(xlUp).Row
    
'   Loop through row all rows backwards, ending at row 5
    For r = lr To 5 Step -1
'       Check values in columns B and E
        If (Cells(r, "B") <> "") And (Cells(r, "E") = "") Then
            Rows(r).Delete
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

 

[Georgiboy]

I was playing with this but @Joe4 beat me to it, however i had amended the code to remove the need to select the sheet and should work no matter what sheet is active:

Sub delete_rows()
    Dim row_count As Long
    Dim i As Long
    Dim ws As Worksheet
    
    Set ws = Sheets("Billing")
    
    With ws
        row_count = .Cells(Rows.Count, "A").End(xlUp).Row
        For i = row_count To 2 Step -1
            If .Cells(i, 2) <> "" And .Cells(i, 5) = "" Then
                .Rows(i).Delete
            End If
        Next i
    End With
End Sub

 

[Jl23]

When deleting rows, it is best to cycle through the range backwards so you are not changing the size of the range you have yet to analyze (by deleting rows and shifting up).
Try this:

Sub Delete_Rows()

    Dim lr As Long
    Dim r As Long
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("Billing")
    ws.Activate
   
    Application.ScreenUpdating = False
   
'   Find last row with data in column B
    lr = Cells(Rows.Count, "B").End(xlUp).Row
   
'   Loop through row all rows backwards, ending at row 5
    For r = lr To 5 Step -1
'       Check values in columns B and E
        If (Cells(r, "B") <> "") And (Cells(r, "E") = "") Then
            Rows(r).Delete
        End If
    Next r
   
    Application.ScreenUpdating = True
   
End Sub

Thank you!! That worked!! Trying to figure out where my code went wrong- is having the <> instead of = for column B why it wasn't picking up the rows? I thought I had it looping backwards but clearly not! Thanks again!!!

 

[Jl23]

I was playing with this but @Joe4 beat me to it, however i had amended the code to remove the need to select the sheet and should work no matter what sheet is active:

Sub delete_rows()
    Dim row_count As Long
    Dim i As Long
    Dim ws As Worksheet
   
    Set ws = Sheets("Billing")
   
    With ws
        row_count = .Cells(Rows.Count, "A").End(xlUp).Row
        For i = row_count To 2 Step -1
            If .Cells(i, 2) <> "" And .Cells(i, 5) = "" Then
                .Rows(i).Delete
            End If
        Next i
    End With
End Sub

Thank you so much!!! It worked great! Comparing to what I had to see where I went wrong

 

[Joe4]

You are welcome.
Glad we were able to help!

 

[Georgiboy]

Thank you so much!!! It worked great! Comparing to what I had to see where I went wrong

I think @Joe4 mentioned earlier, the main difference between yours and mine and @Joe4's code is that you were looping forwards through the range and we are looping backwards. There are ways to do this without a loop but as you had already brought a loop to the table we thought we would help with that.

In the simplest terms if you delete rows when looping through a range forwards then excel gets confused as to what row in the loop it is refering to and can get caught in an infinate loop or just simply delete the wrong rows.

Another method other than looping might be to use Autofilter and delete visible rows, the method used may vary depending on the size of the data or the person writing it.

 

[Jl23]

I think @Joe4 mentioned earlier, the main difference between yours and mine and @Joe4's code is that you were looping forwards through the range and we are looping backwards. There are ways to do this without a loop but as you had already brought a loop to the table we thought we would help with that.

In the simplest terms if you delete rows when looping through a range forwards then excel gets confused as to what row in the loop it is refering to and can get caught in an infinate loop or just simply delete the wrong rows.

Another method other than looping might be to use Autofilter and delete visible rows, the method used may vary depending on the size of the data or the person writing it.

Gotcha, thank you again!!!!

 

[Georgiboy]

Reading your message back and my spelling is terrible...

Happy to help

 

[Joe4]

In the simplest terms if you delete rows when looping through a range forwards then excel gets confused as to what row in the loop it is refering to and can get caught in an infinate loop or just simply delete the wrong rows.

I have not really seen that. It actually follows a logical pattern, and it is usually that is misses rows that should be deleted, if there are multiple consecutive rows that need deleting. That is because you are looping from low to high (if going forwards) while deleting rows shifts the rest of your range up (going from high to low).

This can be shown with a simple example. Let's say that you have three rows (1-3) and the first two should be deleted, but the third should not. So it looks something like this:

Now, let's look at what happens as we loop through forwards. The loop would be from 1 to 3.

Loop 1:
Looks at row 1, and determines that row 1 should be deleted.
So it deletes row 1 and shifts up the data like this:

Loop 2:
Looks at row 2, and determines that row 2 should NOT be deleted (as the other row we really want to delete has shifted up to row 1).
So our data set remains unchanged.

Loop 3:
Looks at row 3, which is blank/empty, so once again the data set remains unchanged.
So we are left with this at the end, where only 1 one of two rows that should have been deleted were actually deleted.

So you can see how consecutive rows to be deleted get missed, as the second in the consecutive row gets moved up to the row you just deleted (so this row gets passed over).
By looping through the range backwards, you are looping in sync (backwards) with how the data is shifting after deletion (moving up).