0

executing the code below allows me to delete rows based on a certain column in that row being a certain value, but deleting the row essentially puts the cell in the row below where it should be. Then wen "next cell" is executed, im moving 2 rows ahead and i skip the one I actually want to check. Is there a way to put a line of code indicated below to bring back the reference to where I want it if the "cell.EntireRow.Delete" line is executed?

Sub DataRedux()
    Dim cell As Range, i, j As Long
    i = Range("a2").Value
    For Each cell In Range("a2:a9999")
        If cell.Value = i Then
            cell.EntireRow.Delete
            'looking for a line of code to put here to account for the index jump created by deleting the row
        Else: Exit For
        End If
    Next cell
End Sub
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
Azhutch3
  • 1
  • 1
  • Instead of using `For..Each` use an indexed loop that starts at the end and increments backwards so that you do not have "index jump" issues. – braX Nov 09 '21 at 21:48
  • or use a filter and delete all the visible rows in one sweep. – Scott Craner Nov 09 '21 at 21:49
  • 1
    Does this answer your question? [Deleting Rows with Reverse Loop - VB](https://stackoverflow.com/a/56473939/8422953) – braX Nov 09 '21 at 22:36

0 Answers0