Loop through each row of a range in Excel

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...

485308 次浏览
小开

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
小开
最佳答案 
Dim a As Range, b As Range


Set a = Selection


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

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
小开

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.


提交答案