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.

Answer 1

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.

Answer 2

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

Answer 3

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