We help IT Professionals succeed at work.

Loop through non-contiguous range using Range.Cells

kgerb
kgerb asked
on
Medium Priority
1,338 Views
Last Modified: 2012-06-22
I'm trying to cycle through all the cells in a non-contiguous and I don't know why this isn't working...
Sub CycleRangeFori()
Dim i As Long
For i = 1 To Range("rngA").Cells.Count
    Range("rngA").Cells(i).Select
Next i
End Sub

Open in new window

Interestingly, this works...
Sub CycleRangeForEach()
Dim rng As Range
For Each rng In Range("rngA")
    rng.Select
Next rng
End Sub

Open in new window

See attached workbook.  The named range is A1:F1,A5:F5.  Why can't I do it using the Range.cells approach?

Kyle
Cycle-through-non-contiguous-ran.xlsm
Comment
Watch Question

CERTIFIED EXPERT

Commented:
In the first code block i is a number

'so range
("rngA").Cells(i).Select

Open in new window


Is trying to put a number in there.  when proper notation is either R1C1 or A1 ie:
ActiveSheet.Cells(5, 4).Select

Open in new window

or
ActiveSheet.Cells("A1").Select

Open in new window


In the other case your using a range object.  
CERTIFIED EXPERT

Commented:
maybe you could do it with offset or resize.

Mechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013
Commented:
Kyle,
Take a look at the Areas collection belonging to your range. You need it because you have non-contiguous selections:

Sub CycleRangeFori()
Dim i As Long, j As Long
For i = 1 To Range("rngA").Areas.Count
    For j = 1 To Range("rngA").Areas(i).Cells.Count
        Range("rngA").Areas(i).Cells(j).Select
    Next j
Next i
End Sub

Open in new window


Brad
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
ScriptAddict,
The reference to .Cells(i) is acceptable. Try stepping through the CycleRangeFori macro.

Kyle,
If you do step through the CycleRangeFori macro, you will see that 12 cells are selected: A1:F1 and then A2:F2. The second block of six cells are selected relative to the first area in the Range--hence the mistake.

The For Each approach works regardless, as you have found out.

Brad
CERTIFIED EXPERT

Commented:
Ack I learned something, guess I'm good for today, gonna head home now :)
kgerbChief Engineer

Author

Commented:
@Brad,
Ok, yeah, I should have remembered to use Areas.  As you have pointed out, Cells A1:F1 are selected and then cells A2:F2 (instead of A5:F5).  I'm trying to figure out why it's doing that and not just failing outright.  You said...

"The second block of six cells are selected relative to the first area in the Range"

Can you explain that?

Kyle
kgerbChief Engineer

Author

Commented:
@ScriptAddict,
yep, stick around and I'm sure you will find out Brad had forgotten more about this stuff than you and I will ever learn.  He has taught me a TON.  He even answered my first question.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_20830519.html

Man was I a NEWB!!!

Kyle
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
Kyle,
VBA doesn't check whether you have exhausted the number of cells in the first Area (or Range) when you ask for the ith cell in the range. So .Cells is iterating with respect to the first Area only: A1:F1. When that first group of six cells is exhausted and your code asks for a seventh, VBA starts looking at A2:F2 and then A3:F3, etc.

I often take advantage of this fact in my code by setting a range variable pointing to the top left cell in a range. I can then use .Cells(i, j) to refer to cells in the ith row and jth column relative to that starting point.

Brad
kgerbChief Engineer

Author

Commented:
Ahh hah, yes.  That makes total sense.  I think I actually remembering reading that in a Walkenbach book once up a time.  Thank you again for the explanation.  Crystal clear now.

Kyle
Most Valuable Expert 2012
Top Expert 2012

Commented:
Wouldn't this work as well?

Sub CycleRangeFori()
Dim i As Long, j As Long
For i = 1 To Range("rngA").Rows.Count
    For j = 1 To Range("rngA").Columns.Count
        Range("rngA").Cells(i,j).Select
    Next j
Next i
End Sub
Most Valuable Expert 2012
Top Expert 2012

Commented:
Well, that was a useful post - please disregard - for each in range or Areas approach appear to be the ticket, lol

Dave
kgerbChief Engineer

Author

Commented:
Sorry it took so long for me to close this question. I kind of forgot about it.  Thanks again for sharing your wealth of knowledge.

Kyle