Is there any way to loop through each cell in a range by columns first? e.g. A1, A2, A3, A4 rather than A1, B1, C1, D1
I tried transposing the range like, but alas this does not work
For Each cell In application.transpose(.Range("C3:G100"))
I have a feeling I am going to have to use something like
for c = 0 to .Range("C3:G100").columns.count
for r =0 to .range("C3:G100").rows.count
Replace
For Each cell In application.transpose(.Range("C3:G100"))
'Your Code here
Next cell
with
Dim col As Range
For Each col In Range("C3:G100").Columns
For Each cell In col.Cells
'Your Code here
Next cell
Next col
dim ws as worksheet
set ws = ActiveSheet ' change here as required
For icol = 1 to n '(change here to max)
For irow = 1 to n '(change here to max)
'some code here
' access cells as ws.cells(irow, icol)
nex irow
next icol
Put it in an array.
Dim i As Long, j As Long
Dim avArray As Variant
avArray = Range("C3:G100").Value
For j = LBound(avArray, 2) To UBound(avArray, 2)
For i = LBound(avArray, 1) To UBound(avArray, 1)
avArray(i, j) = CStr(avArray(i, j)) 'do something here, this
'example just makes the
'value a string
Next
Next
If you're making changes then just dump the array back to the sheet once you're done
Range("C3:G100").Value = avArray
Try this. Below macro will:
(*) in the Active Worksheet:
(1) identify the range of all non-empty cells,
(2) loop through that range column-by-column,
(3) loop through each column cell-by-cell
Sub LoopThruUsedRangeColByCol()
Dim rngCol, rngAll, cell As Range, cnt As Long
Set rngAll = Range("A1").CurrentRegion
'MsgBox R.Address(0, 0), , "All data"
cnt = 0
For Each rngCol In rngAll.Columns
rngCol.Select
For Each cell In Selection
cnt = cnt + 1
Debug.Print (cell.Address)
Debug.Print (cell.row)
Debug.Print (cell.Column)
If cnt > 3 Then End
Next cell
Next rngCol
End Sub
(1) cnt added only so the output does not overwhelm. Remove If cnt > 3 Then End after demo.
(2) you need to open the "immediate window" to see the debug.print output.
To do this: Menubar => View menu => Immediate Window
