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

excel vba delete rows in range
excel vba delete filtered rows but not header
vba for loop delete row
excel vba delete rows with specific data
excel vba delete filtered rows in table
entire row delete vba not working
macro for deleting rows in excel
delete last row vba

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

Deleting rows (working backwards), but use a range use range variables a bunch, but when wanting to remove rows from that range, have to  Then, using that info, we create a Range variable called rngDataBlock to store the full block of data. Lines 25-30 cover our Step 3 – Execution , and even though this section is short there is actually a TON of stuff going on, so let’s walk through it very slowly.


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.

Deleting. Blank. Rows. Work with Excel long enough, and you'll find out that We then establish a counter that starts at the last row of the used range to Sub Macro37() 'Step1: Declare your variables. UsedRange 'Step 3: Start reverse  Argh! Every time you delete a row, the index increases — which means you might miss a bad row. The fix is to work backwards, with your For loop starting at the bottom and working up to the top: Here’s a link to the code above so you can review it side-by-side with the walk through below. Right-click, “Open in new window”.


'Step 4: If entire row is empty then delete it. If Application. This is an object variable that defines your target range. knows you are going to increment the counter backward, moving back one increment on each iteration. When working with a range, you can explicitly call out a specific row in the range by passing a row  deleting rows i am working on extracted files from our system.some informations are not needed. i am using delete every other row but it seems it doesn't work. i want to delete the payment details and remain the paid item. paid item is at A1 and the payment is at the A2 section. i want to delete A2, A4, A6..and so on


This article explains how to delete rows in Excel with VBA using a For loop. is to work backwards, with your For loop starting at the bottom and working up to the Range("F" & .Rows.Count).End(xlUp).Row. 'Loop from the BOTTOM to the top, We create a Worksheet variable for the “data” sheet, which will come in handy​  We can use Delete method of Rows to delete the rows in a range. In this example we will see how to delete the rows from a range in excel worksheet using VB. Excel VBA Macro code for deleting rows in a range should work for all the version of Microsoft Excel 2003, Excel 2007, Excel 2010, and Excel 2013.


Delete rows where the cell value is less than some number section is dimensioning variables, but on Line 9 we do a little up-front work and assign wksData to  Excel VBA Delete Row. Normally in excel worksheet, we have two different methods to delete rows one being the keyboard shortcut and another by using the right-click and insert method but in VBA we have to use delete command and worksheet statement to delete any rows together, the trick to this is that if we need to delete a single row we give a single row reference but for the multiple columns


Learn more about delete row, specific value range MATLAB and Your loop must fail, because it checks all rows of teh matrix, but this matrix Alternatively you could run the loop from size(nmat, 1) backward to 1. There are clearer alternatives and most people who use it don't understand how it works. For Each "group" of No (ex: Range("A2:A5")) I need to look If Each cell in Range("B2:B5") have the same value/locations If they have the same value/locations, I need to delete the entire block (Rows("2:5") Else I go to the next block of No and repeat the operation until the end of the table Since I am deleting rows, part of the code