Loop & If & Delete row

[UberFrank]

Hey guys ive taken a secion out of my code as below

Sub FindFormat()
    
    Dim varMassRange As Range, varCell As Range
    Set varMassRange = Range("D2:D500")
    
    For Each varCell In varMassRange
    varCell.Select
    
    If Selection.Value = "Y" Then
    ActiveCell.EntireRow.Delete
    End If
        Next varCell
End Sub

Now, when there are to rows together both containing a Y the second is ignored and not deleted, i believe this is because the second row then takes on the old row number and excel acts as if it has already checked that cell. I have tryed adding a (-1,0) offset after the delete command but excel still skips to the next row.

Any ideas?

Thanks

 

[villy]

I suggest and most user do when you are deleing row(s) starts to the last one upto the first.

For i = cells(rows.count, 2).end(xlup).row to 1 step -1
cells(i ,2).entirerow.delete
next i

 

[Arul.rajesh]

you could loop from the maxrow to minrow with step -1

Sub Delete_rows()
For i = 500 To 2 Step -1
If Cells(i, 4).Value = "Y" Then
Rows(i).Delete
End If
Next i
End Sub

this way after it deletes the 500th row, row 501 becomes row 500, but since it has already checked row 500, it wont check the row again

If you loop increasing order

After deleting row 2, row 3 will become row 2 and since it has already checked row 2(which is actually row 3), the macro would ignore it.

 

[UberFrank]

Thanks guys i had been looking for a way to reverse the loop as suggested, this should resolve the issue.

Many thanks

Best regards