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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[excel] Loop through each row of a range in Excel

Examples related to excel

Examples related to vba

Examples related to loops

Examples related to excel-2003