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

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 2

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