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

Question

Let's say I have the following code:

Sub TestRangeLoop()
    Dim rng As Range
    Set rng = Range("A1:A6")

    ''//Insert code to loop through rng here
End Sub

I want to be able to itereate through a collection of Range objects for each cell specified in rng. Conceptually, I'd like to do it like so:

For Each rngCell As Range in rng
     ''//Do something with rngCell
Next

I know I could solve this by parsing rng.Address and building Range objects manually, but I'm hoping there is a more direct way that doesn't involve string parsing.

Answer 1

You could use Range.Rows, Range.Columns or Range.Cells. Each of these collections contain Range objects.

Here's how you could modify Dick's example so as to work 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

And 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

Answer 2

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

Answer 3

To make a note on Dick's answer, this is correct, but I would not recommend using a For Each loop. For Each creates a temporary reference to the COM Cell behind the scenes that you do not have access to (that you would need in order to dispose of it).

See the following for more discussion:

http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects-in-c

To illustrate the issue, try the For Each example, close your application, and look at Task Manager. You should see that an instance of Excel is still running (because all objects were not disposed of properly).

A cleaner way to handle this is to query the spreadsheet using ADO:

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