Range Variables and Deleted Cells

Here’s a stackoverflow question that I’ve never seen before. And I’ve seen a lot of questions.

Set a Range variable that holds its state, such as a module-level variable. Then delete the row. Then inspect that variable in the Locals Window.

Option Explicit

Dim r As Range

Sub starttest()
   
    Set r = Sheet1.Range("A1")
   
End Sub

Sub stoptest()
   
    Stop
   
End Sub

I ran starttest, deleted row 1, then ran stoptest. The Locals Window looks like this:

It still points to a memory address and is not Nothing:

I think I’ve never seen this before because I’ve never had a range variable other than as a local variable in a procedure. It seems strange to me that I’ve never encountered that. I guess it could be useful to check if range was deleted. Anyway, I thought it was interesting.

15 Comments

  1. You don’t have to have a module variable for that to happen, as you saw, the pointer is still valid, but points to garbage. It can be a hard bug to track.

  2. Dick Kusleika says:

    I was thinking that you could delete a cell while code was running. But you can delete it via code, which I didn’t consider.

  3. Rob Bovey says:

    When you delete a cell the reference goes bad, but in a strange way. Try entering the numbers 1 through 10 in range A1:A10 and then run the following code:

    Sub Test()
    Dim rngCell As Excel.Range
    For Each rngCell In Sheet1.Range(“A1″).CurrentRegion
    rngCell.EntireRow.Delete
    Next rngCell
    End Sub

    It deletes every other row, so somehow the “Next rngCell” command was able to take a bad pointer and make something consistent out of it.

  4. Rob Bovey says:

    Hi Terry,

    Yes, we’ve had to count backwards in order to do this correctly since the dawn of VBA, but why? Looks like a design flaw to me. The existing behaviour is useless. It appears to do something and then inexperienced developers (or non-developers just trying to get something done) struggle to figure out why it doesn’t do what it seems like it should do. I think a run-time error would have been much more informative.

  5. Dick Kusleika says:

    I also think it’s an unintended side effect of how For Each is compiled. It has to be just like For Next where the offset from the start is correct, but since everything shifted it now points to the wrong place.

    For Each rCell in Sheet1.Range("A1:A3").Cells
        rCell.EntireRow.Delete
    Next rCell

    It must evaluate Sheet1.Range("A1:A3").Cells and make an array of pointers to each object. Then it keeps an internal counter for the loop and offsets from the first pointer. On the second iteration, it would offset +1 from the first pointer, which would point to the new A2 (the previous A3). But that would mean that the array of pointers would have to be re-evaluated every iteration, which doesn’t seem right.

  6. Rob Bovey says:

    Hi Dick,

    I hadn’t really dug into this and when I did it gets interesting in a geeky kind of way. The following test proves that VBA uses rngCell as a throwaway pointer. Notice how I try to set it to refer to A1 right before “Next” and it has no affect.

    Sub Test()

        Dim rngCell As Excel.Range
        Dim rngTable As Excel.Range
       
        Set rngTable = Sheet1.Range("A1:A10")
       
        For Each rngCell In rngTable
            Debug.Print rngCell.Address, rngTable.Address
            rngCell.EntireRow.Delete
            Set rngCell = Sheet1.Range("A1")
        Next rngCell

    End Sub

    It also proves that VBA is dynamically updating the target range of the loop (I guess it uses some internal structure when you use somthing like Sheet1.Range(“A1:A10″) directly). In the Immediate Window you get:

    $A$1 $A$1:$A$10
    $A$2 $A$1:$A$9
    $A$3 $A$1:$A$8
    $A$4 $A$1:$A$7
    $A$5 $A$1:$A$6

    Where VBA fails is that its pointer tracking doesn’t reflect the fact that the deletion operation has shifted everything back one position, which causes it to skip items (it seems to go strictly by address). I’m even more convinced this is a bug. It’s items that matter, not the fact the point hits each address one time. If the For…Each construct tracks deletions in the target range correctly there doesn’t seem to be any reason why it can’t adjust its pointer correctly.

  7. For the record, I’ve reproduced it.

    The most disturbing aspect is that checking

    Is Nothing

    gives no hint that there’s a problem. As far as I know, there’s no way of detecting the problem without raising and trapping an error.

    Test in the Debug window prior to deleting the row:

    ?typename(r)
    Range

    ?r is nothing
    False

    ?objptr(r)
    266041652

    ?r.address
    $C$3037

    Test in the Debug window after deleting the row:

    ?typename(r)
    Range

    ?r is nothing
    False

    ?objptr(r)
    266041652

    ?r.address
    ---------------------------
    Microsoft Visual Basic
    ---------------------------
    Run-time error '424':

    Object required
    ---------------------------
    OK   Help  
    ---------------------------

    The take-home lesson from this is that I can’t just paste in the same old

    If rng Is Nothing

    and call my code error-tolerant without a second thought.

    …And a question for Rob Bovey: are there real-world consequences to this, in terms of memory leaks and stability?

  8. Dick Kusleika says:

    Hmm, interesting.

    I was surprised that the target range was re-evaluated, but maybe I shouldn’t have been. The Range object is strange in that it’s an object and a collection of objects all in one. Range objects adjust even without loops.

    Sub ShiftingRange()
       
        Dim rRng As Range
       
        Set rRng = Sheet1.Range("A1:A10")
       
        Sheet1.Range("A5").EntireRow.Delete
       
        Debug.Print rRng.Address
       
    End Sub

    will return A1:A9. So rngTable is just a Range object acting like a Range object. Here’s my new theory on what’s happening under the covers.

    Sub TestDelCell()
       
        Dim rCell As Range
        Dim rTable As Range
        Dim lLoopCount As Long
       
        Set rTable = Sheet1.Range("A1:A10")
       
        'For Each rCell In rTable.Cells
        lLoopCount = 1
        Set rCell = rTable.Cells(lLoopCount)

    StartLoop:

            rCell.EntireRow.Delete
       
        'Next rCell
        lLoopCount = lLoopCount + 1
        If lLoopCount <= rTable.Cells.Count Then
            Set rCell = rTable.Cells(lLoopCount)
            GoTo StartLoop
        Else
            GoTo EndLoop
        End If

    EndLoop:
       
    End Sub

    Here’s why that theory is wrong (or at least incomplete).

    Sub DelSheets()
       
        Dim sh As Worksheet
       
        For Each sh In ThisWorkbook.Worksheets
            If sh.Name <> "Sheet1" Then
                sh.Delete
            End If
        Next sh
               
    End Sub

    If For Each just uses an internal counter, this should delete every other sheet. It should Set sh = ThisWorkbook.Worksheets(lLoopCount) but it doesn’t. It deletes every sheet (leaving Sheet1 so I don’t get an error). Surely the Worksheets collection is “adjusted” after a deletion just like a Range “collection” is. I have to believe that For Each compiles down to the same code control structure regardless of the object involved, so why do all the worksheets get deleted but not all the cells?

    I can’t think of a better use of a Friday than thinking about this stuff. :)

  9. Gary Waters says:

    Dick,

    A range object in VBA should behave exactly like in the GUI interface and it is the calculation engine that update’s all the data and cell references (just strings!). Type in cell A12 the formula ‘=SUM(A1:A10)’ or create a named reference pointing to A1:A10. If you delete row one each time, the bottom of the list gets moved up by one until you are left with just one reference range, be it ‘A1′ or ‘A1:A1′. Delete that and you are left with #REF! error or a named range that points to nothing like in VBA! So what are ranges? Just string tables (addresses). If you delete a row or column, it deletes that data and updates any string tables (addresses) that point to that by offsetting them. Any created range references from VBA into Excel are probably just temporary named references that only VBA can see while it’s code is running. So Excel only updates the address’s in the pointers for ranges.

  10. Rob Bovey says:

    Nigel:
    The only time this issue can become a problem is when you delete a Range object that you have previously set a VBA object variable to reference. If you’re going to be deleting ranges (as opposed to clearing them) in a loop then be sure to use a backwards working For…Each loop instead of a forward working For Each…Next loop. Also be sure to reset any range variables outside the loop that may have been affected by the deletion. If you really need to test a specific variable you can attempt to get its Address within On Error Resume Next and see if it works.

    As a note of interest, you can get the opposite problem if you’re inserting rows and don’t do anything to eliminate it. Because everything shifts down when you insert, the next range the loop will evaluate after an insert is the row you just inserted. The following code will create an infinite loop if the value of either cell meets the Mod condition:

    For Each rngCell In Sheet1.Range(“A1:A2″)
    If rngCell.Value Mod 2 = 0 Then rngCell.EntireRow.Insert
    Next rngCell

    Dick:
    I agree with your theory of how VBA is working with ranges under the covers. As to why the same problem doesn’t happen to the Worksheets collection, my guess is because the Worksheets collection debuted at the same time VBA did, so the folks at Microsoft made them work correctly together (and all other similar collections that appeared then or later). They may have been stuck with some legacy behavior with the grid that caused this range object weirdness. Joel Spolsky would be a good person to answer this question.

  11. Colin Legg says:

    Following on from Rob’s point about inserting rows, you can get the same ‘pending disposal state’ range as seen earlier by referencing a range which is then shifted off the worksheet, eg.

    Sub foo()

        Dim r As Range
       
        Set r = Cells(Rows.Count, "A")
       
        Range("A1").Insert shift:=xlShiftDown
       
        Stop
       
    End Sub
  12. frank says:

    <>

    Rob, you wrote in your book (Excel 2003 VBA p. 414) that <> but maybe one should say the default property of the Range is the reference >bold\> to an item. A range seems to refer to items without regard for their identity…

    Range parents are loveless parents: they don’t see their children for whom they really are. Instead they are concerned with silly references, status if you like. If a child disappears but another one fits the reference, they’re happy to adopt the new child without a care in the world for the first one. Because most of us are not brought up this way, this (st)range behavior unsettles us.

    Yes, we think it’s a bad pointer but the Range is ruthless enough to accept any new objet the Application objet throws in its arms.

  13. frank says:

    Quote p. 414: “the Item property is the default property of the Range object”.

    Maybe it should be “the default property of the Range is the reference to an item”.

  14. Nigel Heffernan says:

    Rob –

    Thank for confirming that… And the point about row insertions.

    It worries me, that so few developers know about reverse-traversing collections when there’s a deletion operation. Or, indeed, that *any* coder hasn’t met the problem and thought about the implications.

    Outside that specific coding point, everybone considers the question: “What else will be affected by this action?” so it’s instinctive to think of ranges and names outside the loop which may be affected by deletions and insertions.

    At least, I hope it is.

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: