Order of For Each Loop over a Range

[Ralajer]

Hello all,

I know that this must have been answered before but I can't find the phrasing and syntax to narrow down the search results.

Is there a set systematic order a for each loop will loop through a range?

I've tested it with the following code

Sub testFEL()
    Dim myRange As Range, cell As Range
    Dim i As Integer
    
    Set myRange = Range("A1:D3")
    i = 1
    For Each cell In myRange
        cell.Value = i
        i = i + 1
    Next cell
End Sub

It loops through the columns first A1, B1, C1, D1 then row 2. Is the loop order something that is fixed and can be count on when coding?

Thanks in advance

 

[VoG]

If you want to control the order then you need to loop explicitly

For j = 1 To 4
    For i = 1 To 3
        Cells(i, j).Value = 1
    Next i
Next j

 

[mikerickson]

Yes the loop order can be counted on.

BUT...not all ranges are created equal. One might think that these two loops would output the cells in the same order. That would be wrong.

Dim aRange As Range, bRange As Range
Dim oneCell As Range

Set aRange = Application.Union(Range("A1:A2"), Range("B1"))
Set bRange = Application.Union(Range("B1"), Range("A1:A2"))

For Each oneCell In aRange
    MsgBox "A- " & oneCell.Address
Next oneCell

For Each oneCell In bRange
    MsgBox "B- " & oneCell.Address
Next oneCell

Based on that, you might think the two ranges below would be ordered differently. Not so.

Dim aRange As Range, bRange As Range
Dim oneCell As Range

Set aRange = Application.Union(Range("A1:A2"), Range("B1:B2"))
Set bRange = Application.Union(Range("B1:B2"), Range("A1:A2"))

For Each oneCell In aRange
    MsgBox "A- " & oneCell.Address
Next oneCell

For Each oneCell In bRange
    MsgBox "B- " & oneCell.Address
Next oneCell

Yes you can depend on the order of cells being the same, but is the range really the same?

For rectangular continous ranges, move to the right then move down is dependable.

 

[Ralajer]

Thanks Peter,

That is what I have been doing because I didn't know if I could trust the for each loop's order.

Thanks Mike,

I didn't think that I could depend on it for a multiple area range but I had no idea that the Union method argument ordering made a difference. I had planned to only use this on rectangular continuous ranges but I will keep an eye on how the ranges get defined.

Thanks both of you again for your help.