Removing rows without skipping a row how to?

Question

Below is my code. I am trying to move through some rows, and if there is certain data there, then remove those rows, however the current way I have coded it, whenever I delete a row, I skip the one below it. I changed the range to go high numbered rows to low number rows, but my macro still starts at the top and moves down. I thought if I had it move up, a deletion would not cause it to skip the next item. How can I make it move from the bottom of the list upwards, or what is a better way to do this? My code is below:

Dim lLastRow As Long
Dim num As Integer
Dim name As String
Dim rCell As Range
Dim afCell As Range
Dim rRng As Range
Dim affectedRng As Range

Windows("Most Affected Customer Info.xls").Activate
Worksheets("Sheet 1").Activate
Cells(1, 1).Select
Selection.End(xlDown).Select
lLastRow = ActiveCell.Row

Set affectedRng = Range("A" & lLastRow & ":A2")
'First remove resolved entries
For Each afCell In affectedRng
    If (afCell.Offset(0, 4).Value = "resolved" Or afCell.Offset(0, 4).Value _ = "Resolved" Or afCell.Offset(0, 2).Value = "Resolved" Or afCell.Offset(0, 2).Value = _ "resolved") Then
            afCell.EntireRow.Delete
    End If
Next afCell

Answer 1

You could replace your For Each with a do while, like this:

rowx = 2
Do While rowx < llastrow
    If Range("B" & rowx).Value = "resolved" Then 'replace this with the columns you're checking
        Rows(rowx).EntireRow.Delete
    Else
        rowx = rowx + 1
    End If

Loop

Answer 2

How about going through the list backwards?

EDIT Some code to try

For row = lLastRow To 1 Step -1
    If Range("D" & row).Value = "resolved" Then Rows(row).EntireRow.Delete
Next row

I tested this on a small case with "resolved" in column D and it worked like a champ. You might find that the code both does the trick and reads well.

Answer 3

You need a do loop and to use the row number as you need to manipulate both your current position in the loop and also the end point;

e.g.

Dim lRow as Long
lRow = 1
Do Until lRow > lLastRow
    Set afCell = Cells(lRow ,1)

    If (afCell.Offset(0, 4).Value = "resolved" Or afCell.Offset(0, 4).Value _ = "Resolved" Or afCell.Offset(0, 2).Value = "Resolved" Or afCell.Offset(0, 2).Value = _ "resolved") Then
        afCell.EntireRow.Delete

        'Decrement the last row as we've removed a row
        lLastRow = lLastRow - 1
    else
        'Increment the row number to move to the next one
        lRow = lRow + 1
    End IF
Loop

Note: this is completely untested so you'll need to debug it, but you should get the gist.

Answer 4

You could try to change the If condition with While loop.

For Each afCell In affectedRng
    r = afCell.Row
    c = afCell.Column
    While (afCell.Offset(0, 4).Value = "resolved" Or afCell.Offset(0, 4).Value _ = "Resolved" Or afCell.Offset(0, 2).Value = "Resolved" Or afCell.Offset(0, 2).Value = _ "resolved") 
       afCell.EntireRow.Delete
       Set afCell = Cells(r, c)
    Wend
Next afCell