Loop through each cell in a range of cells when given a Range object
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.
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
Read more... Read less...
You could use
Range.Cells. Each of these collections contain
Here's how you could modify Dick's example so as to work with
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
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
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:
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:
I'm resurrecting the dead here, but because a range can be defined as "A:A", using a for each loop ends up with a potential infinite loop. The solution, as far as I know, is to use the
Do Until loop.
Do Until Selection.Value = "" Rem Do things here... Loop