Loop through each row of a range in Excel

Question

This is one of those things that I'm sure there's a built-in function for (and I may well have been told it in the past), but I'm scratching my head to remember it.

How do I loop through each row of a multi-column range using Excel VBA? All the tutorials I've been searching up seem only to mention working through a one-dimensional range...

http://codevba.com/excel/for_each_cell_in_range.htm#.Xyd8qCgzaUk — Frank Myat Thu, Aug 03 '20 at 04:06

score 174 · Accepted Answer · edited Dec 03 '11 at 01:36

174

Dim a As Range, b As Range

Set a = Selection

For Each b In a.Rows
    MsgBox b.Address
Next

edited Dec 03 '11 at 01:36

Rachel Hettinger

7,927
2
21
31

answered Sep 23 '09 at 00:19

Mike

2,995
1
18
14

score 167 · Answer 2 · edited Feb 16 '11 at 17:43

167

Something like this:

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

edited Feb 16 '11 at 17:43

Community

1
1

answered Sep 22 '09 at 23:58

David Andres

31,351
7
46
36

I tried this and row.Columns worked--not row.Cells – Dan Feb 02 '23 at 00:41

score 11 · Answer 3 · answered Dec 09 '15 at 01:33

Just stumbled upon this and thought I would suggest my solution. I typically like to use the built in functionality of assigning a range to an multi-dim array (I guess it's also the JS Programmer in me).

I frequently write code like this:

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.

Two main advantages to favour it: 1) the array method is **always faster** than looping through a range, 2) it' s simple and you can use it in **both directions** and write the array back after some calculations: `Range("A1:D4") = myarray`. **Note:** `Dim myarray` as variant; pay attention to the fact that it's a *1based* 2dim array by default — T.M., Oct 08 '17 at 12:56

score 7 · Answer 4 · answered Aug 20 '15 at 16:50

In Loops, I always prefer to use the Cells class, using the R1C1 reference method, like this:

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

This allows me to quickly and easily loop over a Range of cells easily:

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

Loop through each row of a range in Excel

4 Answers4

Linked

Related