Loop through each row of a range in Excel

Loop through each row of a range in Excel

Asked on December 21, 2018 in Excel.
Add Comment


  • 3 Answer(s)

    Use this code to Loop through each row of a range in Excel,

    Dim a As Range, b As Range
     
    Set a = Selection
     
    For Each b In a.Rows
        MsgBox b.Address
    Next
    
    Answered on December 21, 2018.
    Add Comment

    Just try this code,

    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
    
    Answered on December 21, 2018.
    Add Comment

    Here is the alternative solution,

      To use the Cells class in loops, by the R1C1 reference  approach,

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

    It will grand to quickly loop over 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
    
    Answered on December 21, 2018.
    Add Comment


  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.