Direction for looping through a Selection.

[Bob J.]

When looping through a selection that spans several rows
and columns, VBA evaluates all the cells in the first
column of the selection and then procedes to the next
column in the selection. Is it possible to loop through
a selection by evaluating all the rows first? For
example, you have the following selection, A110. Using
a For Next loop all, cells A1:A10 will be evaluated first
followed by B1:B10, then C1:C10, etc. I want to be able
to evalute the cells in A11 first, followed by A22,
etc.

 

[Bernie Deitrick]

Bob,

By default, Excel goes through rows first, then columns.

If you want to explicitly control the order of movement, here's some example code:

Sub TryNow()
Dim myCell As Range
Dim i As Integer
Dim j As Long
Dim myRange As Range
Set myRange = Range("A1:B2")

'Default
For Each myCell In myRange
MsgBox myCell.Address
Next myCell

'ColumnWise
For i = 1 To myRange.Columns.Count
For j = 1 To myRange.Rows.Count
MsgBox myRange.Cells(j, i).Address
Next j
Next i

'RowWise
For j = 1 To myRange.Rows.Count
For i = 1 To myRange.Columns.Count
MsgBox myRange.Cells(j, i).Address
Next i
Next j
End Sub

HTH,
Bernie
Excel MVP

 

[Tom Ogilvy]

Sub Tester2()
Dim sStr As String
For Each rw In Selection.Rows
For Each cell In rw.Cells
sStr = sStr & cell.Address(0, 0) & ", "
Next
sStr = Left(sStr, Len(sStr) - 2) & vbNewLine
Next
MsgBox sStr

End Sub