Looping backwards to delete items

[HansV]

If you want to loop through the items of a collection and delete items that meet specific conditions, this should in most situations be done by looping backwards, not forwards.

Let me illustrate this with an example in Excel:

Say that you want to look at rows 2 through 100 of the active sheet, and delete a row if the cell in column C is empty and the cell in column D contains "Yes".

A first thought could be to write code like this:

Code: Select all

Dim r As Long
For r = 2 To 100
  If Range("C" & r) = "" And Range("D" & r) = "Yes" Then
    Range("C" & r).EntireRow.Delete
  End If
Next r

Now suppose that C17 and C18 are both empty and that D17 and D18 both contain "Yes". During the loop, we arrive at r = 17. Both conditions are met, so row 17 is deleted. The loop continues with r = 18. But since we deleted row 17, the original row 18 has shifted upwards and has now become row 17. So the code doesn't check that row anymore, and it doesn't get deleted even though it meets the criteria!

The way around this is to loop backwards:

Code: Select all

Dim r As Long
For r = 100 To 2 Step -1
  If Range("C" & r) = "" And Range("D" & r) = "Yes" Then
    Range("C" & r).EntireRow.Delete
  End If
Next r

Now, the loop first arrives at r = 18. Both conditions are met, so row 18 is deleted. The loop continues with r = 17. This row also meets the conditions, so it gets deleted too, as intended.

A For Each loop always loops forwards, so it may suffer from the same problem. In the same situation as above, the following code will fail to delete row 18 too:

Code: Select all

 Dim oCell As Range
For Each oCell In Range("C2:C100")
  If oCell = "" And oCell.Offset(0, 1) = "Yes" Then
    oCell.EntireRow.Delete
  End If
Next oCell

To delete correctly, you must replace the For Each loop with a backwards For … Next loop:

Code: Select all

 Dim n As Long
Dim oCell As Range
For n = Range("C2:C100").Cells.Count To 1 Step -1
  Set oCell = Range("C2:C100").Cells(n)
  If oCell = "" And oCell.Offset(0, 1) = "Yes" Then
    oCell.EntireRow.Delete
  End If
Next i

Note: For Each loops do not always fail to delete items correctly; it depends on the application. If in doubt, experiment, or use a backwards For … Next loop to be on the safe side.

Thanks to Goshute for suggesting this subject!

[VegasNath]

Thanks for this and previous tutorials Hans, they are great resources to refer back to.

[Doc.AElstein]

I wish I’d seen this one before. I found out the hard way, when I noticed a lot of things I had been doing were well messed up.
In the meantime I lost count how many times I explained why****, or corrected people, to loop backwards when deleting things. I have somewhere to send them now… Hans explanation compliments well how I typically explain it...

****
Some people understand a bit better or quicker when I explain it in simple words first:- … like …_
_… if I am stepping forwards, considering things such as looking at contents in a cell, and I delete something, like the cell where I am standing and considering, I end up standing in a void or hole for a split second, until everything in front of me slips back to fill the hole and squashes me.
(A Spreadsheet can’t maintain a void / “black hole” – it’s unstable, so what Excel does is push in a new cell or new row at the end ( bottom or far right ) of the spreadsheet which will shift ( back up or back to the left ) all the cells as far as the void/ “black hole”. So very quickly the hole aint there any more, and I am buried alive. )
That’s quite painful when I get squashed/ buried alive, but I climb or dig my way out and move on to the next to consider. The problem is that the cell which squashed me, is just behind me now, so I don’t get to consider it. That’s the problem. That might have been a cell , or a cell in a row, that should have been deleted. It was the next cell that I should have considered. But it gets missed now.
_... if I am doing the same but going backwards, I still would get squashed when something is deleted. But when I climb out and move on to the next, the next I move on to was not part of what was shifted. It is still where it was. It gets considered. It is the next one to be considered, just as it was intended. All is well. ( Apart from I am bit bruised by all this hole digging https://imgur.com/mQaYoRJ )

[Jay Freedman]

> Note: For Each loops do not always fail to delete items correctly; it depends on the application.

At least in Word, I've found that some collections will process For/Next deletion loops correctly and other collections fail. Evidently it depends on how the particular collection is implemented. It's always safest to assume that they will all fail -- if not in the current version, then possibly in a later version of the object model.