For Each Loop stops after deleting one row

Question

I am trying to use an if else statement to delete the entire row when a text value is found in a cell within a given range.

The code stops after deleting one row.

Sub DeleteSpecificRow()
    Set RR = Range("C1:C8")
    For Each cell In RR
        If cell.Value = "Site subcode " Or cell.Value = "Sample type" Or cell.Value = "Lab Sample ID code" Or cell.Value = "name determinand" Then cell.EntireRow.Delete
    Next cell
End Sub

I tried without using CELL at the end of the next but had the same affect.

Answer 1

Try to loop in reverse, because if row is deleted address of all rows below also changes, which result your RR range obsolete and code stops. (e.g. if row 3 is deleted, row 4 becomes the new row 3, while your RR range points to the old address.)

Sub DeleteSpecificRow()
    Set RR = Range("C1:C8")
    With RR
        For i = .cells.count to 1 step -1
            If .cells(i).Value = "Site subcode " Or .cells(i).Value = "Sample type" Or .cells(i).Value = "Lab Sample ID code" Or .cells(i).Value = "name determinand" Then .cells(i).EntireRow.Delete
        Next
    End with
End Sub