Quite a few questions on Stack Overflow ask about looping through rows and deleting them. The basic question is, “Why is this code only deleting some rows?”:
For Each cell In rng
If cell.value = ""
cell.EntireRow.Delete
End If
Next cell
The problem is that the For/Each loop acts like an incrementing For/Next loop, such as:
If Activesheet.Cells(i,1) = ""
Activesheet.Cells(i,1).EntireRow.Delete
End If
Next i
This means that if it deletes row 2, then row 3 becomes row 2. Meanwhile i
increments to 3, so i
and the blank cell skip past each other like two contra dancers:
The most basic solution – but not the best one – is to loop backwards through the range. Since you can’t do that with a For/Each loop, you’d use a For/Next and alter the incrementing line to:
The reason that’s not a great answer is it’s slow. It’s much faster to identify the entire range to be deleted and then do so in one swell foop. If the criterion for deletion is blanks, for example, you can use SpecialCells, If SpecialCells doesn’t cover it you can build the range with the Union function (that Tim Williams writes some nice code).
Okay, so, that’s useful stuff. The point of this post is less so, but maybe more interesting. My question is:
My Question
Why does a Range collection behave differently than other collections, such as worksheets or workbooks, when deleting items from it?
For example, the code below, which uses an index, deletes every other worksheet because after the first worksheet is deleted the former Worksheets(2) becomes Worksheets(1) and is skipped over by the loop. By now that should be no surprise:
Dim i As Long
For i = 1 To ActiveWorkbook.Worksheets.Count
Application.DisplayAlerts = False
ActiveWorkbook.Worksheets(i).Delete
Application.DisplayAlerts = True
Next i
End Sub
However, a For/Next loop on worksheets works as you’d want it to, with no skipping. It’s like it figures out in advance which sheets your dealing with and remembers them:
Dim ws As Excel.Worksheet
For Each ws In ActiveWorkbook.Worksheets
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
Next ws
End Sub
The range collection seems to be the oddball – the only one that processes a For/Each just like a For i = 1 to whatever loop.
Interestingly, if you set a separate range to cells within that same loop it adjusts just fine. For example, consider this code, which does a For/Each through a range of cells, and also assigns the last cell in the range to a separate LastCell
range. The For/Next skips past cells as before, but the LastCell range moves up one row with each deletion and shifts from A10 to A9, A8 and so on:
Dim ws As Excel.Worksheet
Dim rng As Excel.Range
Dim cell As Excel.Range
Dim LastCell As Excel.Range
Set ws = ActiveSheet
Set rng = ws.Range("A1:A10")
Set LastCell = ws.Range("A10")
rng.Formula = "=Row()"
rng.Value = rng.Value
For Each cell In rng
rng.Interior.ColorIndex = -4142 'none
cell.Interior.ColorIndex = 6 'red
LastCell.Interior.ColorIndex = 3 'yellow
cell.Delete xlUp
Next cell
End Sub
So why don’t cells in the For/Next loop behave as nicely?
The range object sure is strange.
The internal counter for For Each iterations is the NewEnum method in the class. The Worksheets class has a NewEnum that iterates through and returns Worksheet objects. The difference between a Worksheet and a Range is that a Worksheet is only ever one thing. A Range, on the other hand, can be a lot of different things. So much so that there isn’t a Ranges collection object that follows the Worksheet/Worksheets paradigm.
So my guess is that the NewEnum method in the Range class looks nothing like NewEnum in any other class. It probably has to re-evaluate what the Range object refers to every time it’s called, which resets which of its children it’s pointing to.
I would have liked to have been a fly on the wall when they were discussing how to implement the Range object back in the day. A lot of trade-offs had to be made and, on balance, I think they did it right. I wonder if making For Each work like we want had some negative consequences or if the way it works was itself an unintended consequence.
Thanks for the explanation Dick.
I, of course, remember that post, along with the ones where you built your own range object or something like it. I agree, ranges are strange and wondrous things that work amazingly well, evidenced by the fact that the thing I’m kind of complaining about here is something I don’t do anyways cause there’s better ways.
It is fun to hack around in and write about.