Non-contiguous For Each loop per row instead of column

Question

I have a non-contiguous selection spanning rows and columns, and I want to do a For Each loop on it. Excel VBA does this by looping firstly down column 1, then 2,3 etc.; but I want it to loop along the row first instead.

(My sheet looks something like the picture below, I need to loop down the selection (version) each column in turn, and retrieve the Doc. No. and other information. The number of rows and version columns in the sheet is not fixed).

Short of writing a fairly large Sort function and creating an array of references, I was wondering if there was a 'built-in' way to do this?

I don't need code, just an explanation.

Loop columns and then nest a row loop. You can use `Offset` to move columns over on the same row to refer to other cells. — urdearboy, Jan 16 '19 at 14:56
I wonder if named ranges would assist or not. I havent tried it but maybe you could look into it — Doug Coats, Jan 16 '19 at 14:56
check this out https://stackoverflow.com/questions/13039437/loop-through-cells-in-named-range — Doug Coats, Jan 16 '19 at 15:03
Please [edit] your post to include your `For Each` loop... and describe the shape of the range being iterated, or include a screenshot. — Mathieu Guindon, Jan 16 '19 at 15:14

Mathieu Guindon · Answer 1 · 2019-01-16T16:06:55.473

The order in which a For Each iterates an object collection is implementation-dependent (IOW blame Excel, not VBA) and, while likely deterministic & predictable, there is nothing in its specification that guarantees a specific iteration order. So VBA code written to iterate an object collection, should not be written with the assumption of a specific iteration order, since that's something that can very well change between versions of the type library involved (here Excel's).

It's very unclear what the shape of your Range / Selection is, but if you need to iterate the selected cells in a specific order, then a For Each loop should not be used, at least not for iterating the cells per se.

Since the ranges are not contiguous, the Range will have multiple Areas; you'll want to iterate the Selection.Areas, and for each selected area, iterate the cells in a particular order. For Each is, by far, the most efficient way to iterate an object collection, which Range.Areas is.

Debug.Assert TypeOf Selection Is Excel.Range

Dim currentArea As Range
For Each currentArea In Selection.Areas
    'todo
Next

Instead of nesting the loops, make a separate procedure that takes the currentArea as a parameter - that procedure is where you'll be iterating the individual cells:

Private Sub ProcessContiguousArea(ByVal area As Range)
    Dim currentRow As Long
    For currentRow = 1 To area.Rows.Count
        Debug.Print area.Cells(currentRow, 1).Address
    Next
End Sub

Now the outer loop looks like this:

Debug.Assert TypeOf Selection Is Excel.Range

Dim currentArea As Range
For Each currentArea In Selection.Areas
    ProcessContiguousArea currentArea
Next

The ProcessContiguousArea procedure is free to do whatever it needs to do with a given contiguous area, using a For loop to iterate the range by rows, without needing to care for the actual address of the selected area: using Range.Cells(RowIndex, ColumnIndex), row 1 / column 1 represents the top-left cell of that range, regardless of where that range is located in the worksheet.

Non-selected cells can be accessed with Range.Offset:

        Debug.Print area.Cells(currentRow, 1).Offset(ColumnOffset:=10).Address

The top-left cell's row of the area on the worksheet is returned by area.Row, and the top-left cell's column of the area on the worksheet is retrieved with area.Column.

VBasic2008 · Answer 2 · 2019-01-16T17:50:51.510

1

Non-Contiguous

By looping through the rows first (i), you will get the 'By Row sequence' e.g. A1,B1,C1, ...

The Code

Sub NonContiguous()

    Dim i As Long
    Dim j As Long
    Dim k As Long

    With Selection
        For k = 1 To .Areas.Count
            With .Areas(k)
                For i = .Row To .Rows.Count + .Row - 1
                     For j = .Column To .Columns.Count + .Column - 1

                         Debug.Print .Parent.Cells(i, j).Address & " = " _
                                 & .Parent.Cells(i, j)

                     Next
                Next
            End With
        Next
    End With

End Sub

edited Jan 16 '19 at 17:50

answered Jan 16 '19 at 15:42

VBasic2008

44,888
5
17
28

3

I'm very tempted to upvote, for this is a very similar approach to mine (key being `Selection.Areas`, missed by the other answer), however triple-nested loops and repetitive dereferencing of the `.Areas(k)` reference, combined with the poor variable names and confusing data types used (there's not really any reason to use `Integer` anywhere other than in Win32 API calls that require it), are holding me back. `For` loop iterating the areas is a missed opportunity: a `For Each` would dereference the current area only once per iteration, vs. 6 times here. Also the type of `Selection` is assumed. – Mathieu Guindon Jan 16 '19 at 16:04
@Mathieu Guindon: Believe it or not, I have no clue what you're talking about. I am recognizing the following issues you're addressing: 'Repetitive Dereferencing' (probably connected to For Each), 'Integer Anywhere' and 'Type of Selection'. I would appreciate any clarification or links to them. – VBasic2008 Jan 16 '19 at 16:40
Though I understand why i, j, and k are "poor variable names," I just wanted to say that I (also) use those all the time in nested loops. It makes sense to me to iterate through matrices of data with single-letter variables as it's reminiscent of linear algebra. Just my take. – MBB70 Jan 16 '19 at 16:48
1

There's an old, excellent *Coding Horror* [article](https://blog.codinghorror.com/flattening-arrow-code/) about "arrow code" that does a better job than I ever could at explaining how nested structures can be "flattened". @MBB70 we're all guilty ;-) ...I just wish VBA answers on SO upped the bar a bit, so that online references for VBA (esp. for newcomers) illustrate better code than what the "[VBA sucks](https://insights.stackoverflow.com/survey/2018/#technology-most-loved-dreaded-and-wanted-languages)" trope wants us to believe VBA code looks like (I know, wishful thinking). – Mathieu Guindon Jan 16 '19 at 16:58
2

Also [Integer vs Long](https://stackoverflow.com/a/26409520/1188513). As for multiple/repetitive dereferencing, the `With .Areas(k)` block that was added is correctly addressing it, although at the expense of increased nesting. What I meant about the type of `Selection`, is that if the code is executed while the `Selection` is a `Chart` or some `Shape`, then `.Areas` will throw error 438, for member calls against `Selection` are resolved at run-time (since it's `Variant/Object`); assigning it to a `Range`, or `Debug.Assert`ing its type, eliminates the assumption. Anyway, upvoted now :) – Mathieu Guindon Jan 16 '19 at 17:02
@Mathieu Guindon: Many thanks for the extensive help (resources). Referring to **Integer vs Long**: Feels like I've been living under a rock. But the problem is that a large percentage of my posts contain integers. I guess I'll have to do some serious editing. A funny thing is that years ago, I read a similar article: **Byte vs Integer**. – VBasic2008 Jan 16 '19 at 18:07

score 0 · Accepted Answer · answered Jan 16 '19 at 16:17

This is based on urdearboy's suggestion:

1. loop over columns
2. within a column, loop over cells

Sub disjoint()
    Dim r As Range, rInt As Range
    Dim nLastColumn As Long
    Dim nFirstColumn As Long, msg As String
    Dim N As Long

    Set r = Range("C3,C9,E6,E13,E15,G1,G2,G3,G4")

    nFirstColumn = Columns.Count
    nLastColumn = 0
    msg = ""

    For Each rr In r
        N = rr.Column
        If N < nFirstColumn Then nFirstColumn = N
        If N > nLastColumn Then nLastColumn = N
    Next rr

    For N = nFirstColumn To nLastColumn
        Set rInt = Intersect(Columns(N), r)
        If rInt Is Nothing Then
        Else
            For Each rr In rInt
                msg = msg & vbCrLf & rr.Address(0, 0)
            Next rr
        End If
    Next N
    MsgBox msg
End Sub

Non-contiguous For Each loop per row instead of column

3 Answers3

Non-Contiguous

The Code

Linked