Ndtyjky

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

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

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.

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