Delete row based on criteria - Whats wrong in my macro?

[ORoxo]

Hello, everyone,
I am trying to create a macro which is supposed to delete every line which contains the number 5 in column B and I'm almost there - or at least, that's what I think.

This is what I have to far:

Sub brincar()
Dim r As Integer, c As Integer, lc As Integer, lr As Integer


For r = 2 To 9


    If Cells(r, 2) = 5 Then


        ActiveCell.EntireRow.Delete
    
    End If


Next


End Sub

Nonetheless, this code has a problem. Let's say I have titles on row 1 and then both row 2 and 3 have a 5 in column B. The macro would delete the second row so the 5 which was previously in column 3 would go to column 3. However, the For...next would then follow to row 3 ignoring the 5 in row 2.

This might be a bit confusing, but do you understand the problem? How can I write the code so it goes through until the last row which has data and deletes the ones with a 5 in column B?

Thanks,
ORoxo

 

[Fluff]

You need to loop in reverse like

Sub brincar()
Dim r As Integer, c As Integer, lc As Integer, lr As Integer


For r = 9 To 2 Step -1


    If Cells(r, 2) = 5 Then


        Rows(r).Delete
    
    End If


Next r


End Sub

 

[ORoxo]

You need to loop in reverse like

Sub brincar()
Dim r As Integer, c As Integer, lc As Integer, lr As Integer


For r = 9 To 2 Step -1


    If Cells(r, 2) = 5 Then


        Rows(r).Delete
    
    End If


Next r


End Sub

Didn't think about this. Thank you so much, Fluff!!

 

[Fluff]

You're welcome