Deleting rows (working backwards), but use a range variable?

  • 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.


:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: