- A+

Often times it's required that you go through a range of cells, and based on some criteria, delete an entire row.

In practice, it's best to start at the *end* of the range, and work up.

`Dim i as Long For i = lastRow to 1 Step -1 If Cells(i, 2).Value = "del" then Rows(i).EntireRow.Delete End if `

However, most of the time I'm working with a `Range`

object.

Is there a way to work backwards, with a range object, that doesn't require the use of a `For i`

type loop?

`Dim rng as Range, cel as Range Set rng = Range("A1:A100") For each cel in rng step -1 if cel.value = "del" then cel.EntireRow.Delete next cel `

This errors `Expected: End of Statement`

on the `Step -1`

portion, which I expected (no pun intended).

The idea is that I don't have to basically re-arrange my data into `Cells()`

when trying to work backwards on a `Range`

variable. I find it a little kludgy to use range variables a bunch, but when wanting to remove rows from that range, have to switch to using `Cells([long],[long])`

if that makes sense.

Edit: Just came up with this, but it still feels kludgy:

`Dim k As Long, cel as Range Set cel = rng.cells(rng.cells.count) For k = cel.Row To rng.Cells(1).Row Step -1 If rng.Cells(k).Value = "del" Then rng.Cells(k).EntireRow.Delete Next k `

Yes, you can do it without a `For i =`

statement. Just create a special range that you will delete once you finish your loop.

`Dim cel As Range, rng As Range Dim delRng As Range For Each cel In rng If cel.Value = "del" Then If delRng Is Nothing Then Set delRng = cel Else Set delRng = Union(delRng, cel) End If End If Next cel If Not delRng Is Nothing Then delRng.EntireRow.Delete `

And you don't even have to step backwards.