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.

score 88 · Accepted Answer · answered Oct 06 '10 at 18:13

88

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 Oct 06 '10 at 18:13

Dick Kusleika

32,673
4
52
73

1

This worked perfectly, but I'm surpised as to why it worked since `Cells` is just a `Range` object. In fact, I removed `.Cells` from `rRng` in the `For Each` line and it still worked. What about `Range` makes it look like a collection of `Range`s? Thanks so much for your help! – Ben McCormack Oct 06 '10 at 18:25
7

http://www.dailydoseofexcel.com/archives/2004/07/07/the-strange-object/ The Cells property is the default property in this context, so that's why it works without it. In other contexts, the Value property is the default property. All Range objects are really collection objects that contain Range objects - to infinity I guess. The Range is a decidedly strange object and breaks the object/object collection paradigm at every turn. But in most cases, it just works. :) – Dick Kusleika Oct 06 '10 at 19:46
thanks for the additional insight. I wish that I had learned that "it just works" with `Range` about 4 years ago when I started doing VBA programming :-). – Ben McCormack Oct 07 '10 at 01:21
When i use your code it's running successfully then how about printing the value or string of the cells? – Aljie Jan 27 '14 at 04:35
@BenMcCormack `In fact, I removed .Cells from rRng in the For Each line and it still worked`. because a single cell is also a range object and since we have declared rCell as Range it is able to iterate through all the cells reffered by rRng Range. – Rohit Saluja Aug 09 '16 at 04:45

code4life · Answer 2 · 2010-10-06T20:07:57.507

15

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

edited Oct 06 '10 at 20:07

answered Oct 06 '10 at 18:10

code4life

15,655
7
50
82

How would you use those properties to create a loop. I've been playing around with properties of `Range` such as the ones you mentioned and I can't seem to figure out how to use them to give me the information I need. – Ben McCormack Oct 06 '10 at 18:12
Your example does not make sense as there is just one column I gather. – Timo Jun 15 '21 at 09:13
Can I address each column separately? I have a pc column and mailaddress column and want to send emails from excel with pc name to the mailaddress, so I need these 2 values separated. including outlook object works. – Timo Jun 15 '21 at 09:35
@Timo, just remember that from each rCol you can infer the next column by offset (leftwise out rightwise). You can iterate the Rows from those references. – code4life Jul 06 '21 at 22:09

score 4 · Answer 3 · edited May 23 '17 at 12:26

4

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:

How do I properly clean up Excel interop objects?

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

edited May 23 '17 at 12:26

Community

1
1

answered Oct 06 '10 at 18:21

Mark Avenius

13,679
6
42
50

1

I don't think these warnings apply to this question, though. Ben is using VBA, presumably within the same instance of Excel as the Ranges he is working with. – jtolle Oct 06 '10 at 18:25
3

I am just using VBA. I appreciate the word of caution, but this particular solution is of the throw-away I-just-need-to-loop-through-some-cells-to-generate-some-SQL-code-so-I-can-finish-my-script-and-move-with-my-life variety. – Ben McCormack Oct 06 '10 at 18:31
3

Whoops; you are right :-) I assumed you were working with VB.Net and was completely led astray by my own mind. Sorry about that. – Mark Avenius Oct 06 '10 at 18:33
I just commented code4life' s answer, maybe you can help because he has been away from stackoverfl for a time: Can I address each column separately? I have a pc column and mailaddress column and want to send emails from excel with pc name to the mailaddress, so I need these 2 values separated. including outlook object works – Timo Jun 15 '21 at 09:39
I also need a filter: `Set rRng = Tabelle1.Range("g22:h32") For Each rcol In rRng.Columns.SpecialCells(xlCellTypeVisible) For Each rcell In rcol.Rows..` It does not work as I have range objects. I need numbers x,y to address `cells(x,y)` I think. – Timo Jun 15 '21 at 09:50

score 0 · Answer 4 · answered Dec 09 '15 at 00:37

0

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

answered Dec 09 '15 at 00:37

Nielsvh

1,151
1
18
31

3

If you are not using the last populated cell looking from the bottom up (e.g. `.range(.cells(1, 1), .cells(.rows.count, 1).end(xlup))`) then using the [Intersect method](https://msdn.microsoft.com/en-us/library/office/aa195772(v=office.11).aspx) with the full column and the worksheet's [.UsedRange property](https://msdn.microsoft.com/en-us/library/office/ff840732.aspx) (e.g. `Intersect(.columns(1), .usedrange)`) or possibly the [Range.CurrentRegion property](https://msdn.microsoft.com/en-us/library/office/ff196678.aspx) (e.g. `.cells(1, 1).currentregion.columns(1)`) is quite effective. – Dec 09 '15 at 00:51

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

4 Answers4

Linked