# Delete a row from within for each statement

• ### Question

• Hi folks,

I've got the following code

```For Each cell In rng.Cells
cell.Activate
If cell = RemovePartialQtr Then
a = ActiveCell.Row
Rows(a & ":" & a).Delete

End If
Next```

The code works fine in principle.  However, what I'm finding when two consecutive rows meet the criteria for the IF statement, the first one gets deleted, the subsequent rows shift up, and the next row doesn't get analyzed.

So, if rows 2, 3 and 4 each meet the criteria, then 2 would get deleted, rows 3 and 4 become rows 2 and 3 respectively.  The next cycle of the FOR statement analyzes row 3 (which had been row 4), and leaves row 2 (which had been row 3) not being analyzed.

How can I step backwards after the delete statement in my code?

Any help is appreciated!

Thanks!

Casey

Wednesday, January 6, 2016 5:56 PM

• If you delete an item from a collection, that collection has been changed and you can't continue to enumerate through it.

Instead, use a For loop, such as:

```For i = rng.Rows.Count To 1 Step -1

'If cell i in the range contains an "x", delete the entire row.
If rng.Cells(i).Value = "x" Then rng.Cells(i).EntireRow.Delete
Next```

Ashish Pandey

• Marked as answer by Wednesday, January 6, 2016 6:21 PM
Wednesday, January 6, 2016 6:06 PM

### All replies

• If you delete an item from a collection, that collection has been changed and you can't continue to enumerate through it.

Instead, use a For loop, such as:

```For i = rng.Rows.Count To 1 Step -1

'If cell i in the range contains an "x", delete the entire row.
If rng.Cells(i).Value = "x" Then rng.Cells(i).EntireRow.Delete
Next```

Ashish Pandey

• Marked as answer by Wednesday, January 6, 2016 6:21 PM
Wednesday, January 6, 2016 6:06 PM
• That worked perfectly, Ashish.

Thank you!

Wednesday, January 6, 2016 6:21 PM