Why is Deleting Ranges So Backward?

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?”:

Set rng = ActiveSheet.Range("A1:A10")
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:

For i = 1 to 10
    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:

contra dance

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:

For i = 10 to 1 Step -1

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:

Sub WorksheetLoopIndex()
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:

Sub WorksheetLoopForEach()
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:

Sub DeleteIndividualCellsForEach()
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

cell looping

So why don’t cells in the For/Next loop behave as nicely?

2 thoughts on “Why is Deleting Ranges So Backward?

  1. 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.

Speak Your Mind

Your email address will not be published. Required fields are marked *

To post code, do this: <code> your vba here </code>