Loop through each cell in a range of cells when given a Range object

Loop through each cell in a range of cells when given a Range object

Asked on January 8, 2019 in Excel.
Add Comment


  • 3 Answer(s)

    The best solution is here :

    Sub LoopRange()
     
       Dim rCell As Range
       Dim rRng As Range
     
       Set rRng = Sheet1.Range("A1:A6")
     
       For Each rCell In rRng.Cells
           Debug.Print rCell.Address, rCell.Value
       Next rCell
     
    End Sub
    Answered on January 8, 2019.
    Add Comment

    The Range objects contains, group of Range.Rows, Range.Columns or Range.Cells.

    Following code will explain with Rows:

    Sub LoopRange()
     
       Dim rCell As Range
       Dim rRng As Range
     
       Set rRng = Sheet1.Range("A1:A6")
     
       For Each rCell In rRng.Rows
           Debug.Print rCell.Address, rCell.Value
       Next rCell
     
    End Sub
    

    Following code will explain with Columns:

    Sub LoopRange()
     
       Dim rCell As Range
       Dim rRng As Range
     
       Set rRng = Sheet1.Range("A1:A6")
     
       For Each rCol In rRng.Columns
          For Each rCell In rCol.Rows
              Debug.Print rCell.Address, rCell.Value
          Next rCell
       Next rCol
     
    End Sub
    
    Answered on January 8, 2019.
    Add Comment

    Use this solution :

            For Each formulates an unstable reference to the COM Cell after the scenes that do not have access to, that would need in order to settle of it.

    To represent the issue, try the For Each example, close the application, and see at Task Manager. Now can see that an instance of Excel is still running , in that all objects were settled properly).

    By using ADO for cleaner way to hold this is to query the spreadsheet.

    http://technet.microsoft.com/en-us/library/ee692882.aspx

    Answered on January 8, 2019.
    Add Comment


  • Your Answer

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