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