VBA: How to loop through each row in selected range exactly

[Brian]

What is the best code to loop through each row in selected range exactly once?

It should accommodate selecting individual cells, entire rows, entire columns, and non-continuous ranges, which could be a mixture of single cells and rows. (ie. handle anything.)

Situation: I have data in a table. I would like to send one e-mail per row that user has selected. I want to ensure I send an e-mail for every row, but no duplicate e-mails.

I tried a few things, but they all had holes.

Thanks in advance,

Brian


Solved: I figured out a method that works for me:

Set SourceSelection = Selection.EntireRow
Set isect = Application.Intersect(SourceSelection, Range("A3:A65536"))

For Each Cell In isect
CR = Cell.Row
Next Cell

[ This Message was edited by: Brian on 2002-05-13 23:54 ]

[dimrod]

One possible problem with your code is that the user might select a whole column before running the macro.
This would mean that the macro would be looping through 65,534 cells, most of which would presumably be empty.

To remove this possibility, change the first line of your code to :-

Set SourceSelection = Application.Intersect(ActiveSheet.UsedRange, Selection.EntireRow)

[Brian]

Many thanks.