Weird looping through cells in a range object

Below is something that I experienced a while ago and was beyond my logic.
Setting a range and looping through all cells in it didn't work initially as expected.

Sub WeirdCelLooping()
      Dim rngCel                                       As Range
      Dim rngSelection                                 As Range

      Range("A1:G10").Select

      ' Doesn't work correctly:
      For Each rngCel In Selection.Columns(1)
            ' both rngCel and the selection have now the same address
            Debug.Print rngCel.Address & " as part of " & Selection.Columns(1).Address
      Next

      ' Doesn't work correctly:
      Set rngSelection = Selection.Columns(1)
      For Each rngCel In rngSelection
            ' both rngCel and the selection now still have the same address
            Debug.Print rngCel.Address & " as part of " & Selection.Columns(1).Address
      Next

      ' Does work correctly:
      Set rngSelection = Range(Selection.Columns(1).Address)
      For Each rngCel In rngSelection
            ' finally we can loop through each cell in the range
            Debug.Print rngCel.Address & " as part of " & Selection.Columns(1).Address
      Next
End Sub

Update October 24:
As stated in the comments below (thank!); the best practice to loop through a range is to explicitly add the .Cells at the end for the range:
Selection.Columns(1).Cells
rngSelection.Cells

5 Responses to “Weird looping through cells in a range object”

  1. Try:
    For Each rngCel In Selection.Columns(1).cells
    and
    For Each rngCel In rngSelection.cells

  2. That's the goofy nature of the range object, but it's consistent and actually useful in some situations. When you reference Columns(1) you're referencing a range that is a column. Just because you name your variable rngCel doesn't mean that it's a single cell range. Excel fits the variable into the "In" reference's default return range.

    Rows() and Columns() both return the entire row or column (within the range specified). Range() and Areas() return the individual cells by default.

    I've developed the habit of always explicitly calling out the level of detail I want. So I would code

    For Each rng in Range("A1:G10").Cells

    to mean that I want to loop through all cells individually (even though Cells is the default for Range), and

    For Each rng in Range("A1:G10").Rows

    to mean that I want to loop through the rows.

    If you change your first loop to "Selection.Columns(1).Cells", you would force the desired behavior rather than rely on the default for the Columns() range reference.

  3. Don't assume you know what will be the default member of a class. Use these aand the first two willl work properly:

    For Each rngCel In Selection.Columns(1).Cells
    For Each rngCel In rngSelection.Cells

    For further assurance, the third should be:

    For Each rngCel In rngSelection.Cells

  4. I believe what you want to do is specify explicit what collection you want to iterate through:
    [code]
    Sub WeirdCelLoopingFixed()
    Dim rngCel As Range
    Dim rngSelection As Range

    Range("A1:G10").Select

    ' Works correctly:
    For Each rngCel In Selection.Columns(1).Cells
    Debug.Print rngCel.Address & " as part of " & Selection.Columns(1).Address
    Next

    ' Works correctly:
    Set rngSelection = Selection.Columns(1)
    For Each rngCel In rngSelection.Cells
    Debug.Print rngCel.Address & " as part of " & rngSelection.Address
    Next
    End Sub
    [/code]

    I have no explanation for this but at least it works :-)
    (remember to loop through areas also when your working with user selection)

  5. Hello Dave, Dick, Jon and Peder,

    Wow,

    I didn't know about the Range.Cells. I just figured that the "for each" would loop through the tiniest part of the collection: a cell, which it always did until I recently added the .Column(1) part.

    However it seems logical to add the .Cells now that I read it.

    Thank you!

Pinterest