General

Weird looping through cells in a range object

by Bastien 5 Comment

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 comments

Dave

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

Dick Kusleika

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.

Jon Peltier

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

Peder Schmedling

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)

Bastien

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!