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.
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
Filed under: General on October 23rd, 2008 by Bastien |
Try:
For Each rngCel In Selection.Columns(1).cells
and
For Each rngCel In rngSelection.cells
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.
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
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)
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!