Loop through range issue

[Kieran]

Greetings all -

My problem is one of understanding or lack of it! :

I wish to loop through the first column of a range. I set a range
object to equal the first column of my range. I then attempt to loop
through each cell in my now - single column range. Yet for reasons I
don't understand my Cell range is now the same as rRange and the
process fails

Sub xx()
Dim Cell As Range
Dim rRange As Range

Set rRange = Range("A1:B20").Columns(1)
For Each Cell In rRange
Debug.Print Cell.Address
Next
End Sub

However if I do the following it works perfectly

Sub x()
Dim Cell As Range
Dim rRange As Range

Set rRange = Range("A1:B20").Columns(1)
For Each Cell In Range(rRange.Address)
Debug.Print Cell.Address
Next
End Sub

Any thoughts would be appreciated - note that I am keen to understand
the issue not solve it another way

Cheers

Kieran

 

[Don Guillett]

how about this

For i = 1 To 20
MsgBox Cells(i, 1).Address
Next i

 

[NickHK]

Kieran,
I would assume that because you specify .Columns(1), the actual range is
consider to be the whole block (A1:A20), of which the is only 1.

However if you loop through
For Each Cell In rRange.Cells
you are returning to a range of constituent cells and get your desired
results.

Seems like a lame explanation even to me, so hopefully somebody has a better
one.

NickHK

 

[Guest]

From the immediate window:
? Range("A1:B20").Columns(1).Count
1
? Range("A1:B20").Columns(1).Cells.count
20

You have 1 "column" range in the first case.

so you would want:

Set rRange = Range("A1:B20").Columns(1).Cells

 

[Jean-Yves]

Hi Kieran
Interesting
For resizing a range use "resize" instead of using the columns property.
Set rRange = Range("A1:B20").Resize(, 1)

For the why part, could a better person answer this ?
Regards
Jean-Yves

 

[Kieran]

To all:

The mist clears . . . . .

Set rRange = Range("A1:B20").Columns(1)

rRange is a monlithic block containing one cell - Negative outcome -
not good!

Set rRange = Range("A1:B20").Columns(1).Cells

rRange contains all cells as desired.

Yet another case of Pilot Error!

Many Thanks

 

[Alan Beban]

To all:

The mist clears . . . . .

Not quite. After Set rRange = Range("A1:B20").Columns(1)

rRange.Address returns $A$1:$A$20, as does rRange(1).Address.
rRange(2).Address returns $B$1:$B$20, rRange(3).Address $C$1:$C$20, etc.
This might be the monolithicality you were referring to. But rRange
contains not just one cell, but all the cells in rRange, as you can see
from, e.g., rRange.Cells(3).Address, which returns $A$3; you just can't
access the individual cells with rRange(1), rRange(2), etc., because
those index numbers refer to columns, not cells.

Alan Beban