asked on

Loop through non-contiguous range using Range.Cells

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

ScriptAddict

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

ActiveSheet.Cells("A1").Select

Open in new window

In the other case your using a range object.

ScriptAddict

maybe you could do it with offset or resize.

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

byundt

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

ScriptAddict

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

kgerb

ASKER

@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

kgerb

ASKER

@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.

https://www.experts-exchange.com/questions/20830519/Using-Dynamic-Range-to-Fill-Validation-List.html

Man was I a NEWB!!!

Kyle

byundt

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

kgerb

ASKER

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

dlmille

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

dlmille

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

Dave

kgerb

ASKER

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