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

Question

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

Answer 1

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.

Answer 2

The other way round

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

In addition to @K-Davis ' valid solution, I demonstrate how to use the advanced filtering possibilties of the Application.Index method. It only expects three arguments:

• a 2-dim datafield array v based on the entire data set
• a 1-dim array of the row numbers to be maintained, called via a helper function getAr(v, 1), where argument 1 defines the 1st column
• (a 1-dim array of all columns, created automatically via Evaluate)

Instead of deleting rows, this approach used the whole data set (e.g. A2:C10 omitting an assumed title row) and writes back the filtered datafield array assigning it to rng again.

Main procedure

Sub DelRows()
  Dim rng As Range,  v
  Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A2:C10") ' << change to entire data range
' [1a] create 2-dim data field array (1-based)
  v = rng.Value2
' [1b] filter out rows to be deleted
  v = Application.Transpose(Application.Index(v, getAr(v, 1), Evaluate("row(1:" & rng.Columns.Count & ")")))
' [2] write data field back to resized range
  rng = ""                                  ' clear lines
  rng.Resize(UBound(v), UBound(v, 2)) = v
End Sub

Helper function getAr()

Function getAr(v, ByVal colNo&) As Variant()
' Purpose: collect row numbers not to be deleted (criteria no "DEL" in 1st column)
' Note:    called by above procedure DelRows
  Dim ar, i&, n&
  ReDim ar(0 To UBound(v) - 1)
  For i = 1 To UBound(v)
      If UCase$(v(i, colNo)) <> "DEL" Then
         ar(n) = i: n = n + 1
      End If
  Next i
  ReDim Preserve ar(0 To n - 1): getAr = ar
End Function

Related SO link

Cf. Insert new first column in datafield array without loops or API calls

Answer 3

I know you said you don't like For i, but IMHO this is the cleanest way to go

For i = rng.Rows.Count To 1 Step -1
    With rng.Cells(i, 2)
        If .Value = "del" then
            .Entirerow.Delete
        End If
    End With
Next

Note that the rng.Cells construct is relative to rng

Eg if rng is A100:A1000 then rng.Cells(rng.Rows.Count, 1) refers to A1000