Loop through each cell in a range of cells when specifying 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 set of Range objects for each cell specified in rng . Conceptually, I would like to do it like this:

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

I know that I can solve this problem by parsing rng.Address and creating Range objects manually, but I hope there is a more direct way that does not include parsing the strings.

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 can use Range.Rows , Range.Columns or Range.Cells . Each of these collections contains Range objects.

Here you can modify the Dick example 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 3

To make a note about Dick, this is correct, but I would not recommend using a For Each loop. For each, a temporary link is created to the COM cell behind the scenes, to which you do not have access (what you need to get rid of it).

See below for more details:

How to clean Excel interaction objects correctly?

To illustrate this problem, try an example for each example, close the application and look at the Task Manager. You should see that the Excel instance is still working (as all objects have not been deleted properly).

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

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

Answer 4

I raise the dead here, but because the 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 a Do Until loop.

 Do Until Selection.Value = "" Rem Do things here... Loop