Loop through each row of a range in Excel

Question

This is one of those things in which I am sure that there is a built-in function (and I may have already been told this in the past), but I scratch my head to remember it.

How do I scroll through each row of multiple columns using Excel VBA? All the tutorials I was looking for seem to only talk about working with the one-dimensional range ...

Answer 1

Dim a As Range, b As Range Set a = Selection For Each b In a.Rows MsgBox b.Address Next 

Answer 2

Something like that:

 Dim rng As Range Dim row As Range Dim cell As Range Set rng = Range("A1:C2") For Each row In rng.Rows For Each cell in row.Cells 'Do Something Next cell Next row 

Answer 3

I just stumbled upon this and thought that I was proposing my solution. I usually like to use the built-in range assignment functions in a multidimensional array (I assume this is also a JS programmer in me).

I often write code as follows:

 Sub arrayBuilder() myarray = Range("A1:D4") 'unlike most VBA Arrays, this array doesn't need to be declared and will be automatically dimensioned For i = 1 To UBound(myarray) For j = 1 To UBound(myarray, 2) Debug.Print (myarray(i, j)) Next j Next i End Sub 

Assigning ranges to variables is a very powerful way to manipulate data in VBA.

Answer 4

In loops, I always prefer to use the Cells class using the R1C1 reference method, for example:

 Cells(rr, col).Formula = ... 

This allows me to cycle quickly and easily in a range of cells:

 Dim r As Long Dim c As Long c = GetTargetColumn() ' Or you could just set this manually, like: c = 1 With Sheet1 ' <-- You should always qualify a range with a sheet! For r = 1 To 10 ' Or 1 To (Ubound(MyListOfStuff) + 1) ' Here we're looping over all the cells in rows 1 to 10, in Column "c" .Cells(r, c).Value = MyListOfStuff(r) '---- or ---- '...to easily copy from one place to another (even with an offset of rows and columns) .Cells(r, c).Value = Sheet2.Cells(r + 3, 17).Value Next r End With