15 thoughts on “Range Variables and Deleted Cells”

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. 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. 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,
   See this post from May 2004
   http://www.dailydoseofexcel.com/archives/2004/05/17/deleting-cells-in-a-loop/

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

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

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

8. 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?

9. 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. :)

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

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

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

13. <>

    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.

14. 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”.

15. 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? Use <pre> tags for VBA and <code> tags for inline.