Bob Phillips wrote:
I would preferably use For Each as well. One of the advantages is that you
can also modify the range before the loop, such as

Dim rng As Range
Dim cell As Range

With ActiveSheet.UsedRange
On Error Resume Next
Set rng = Union(.SpecialCells(xlCellTypeConstants), _
.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0
End With

If Not rng Is Nothing Then
For Each cell In rng
'do something
Next cell
End If

which goes right against your ... obviously, if ... <vbg

--
HTH

Bob Phillips

Thanks for your replies. This is just what I wanted to know.

It's also a good illustration, at least to me, that the obvious way is
not always the best way. Now I know how to approach this next VBA
project better.


(remove xxx from email address if mailing direct)

"davegb" wrote in message
oups.com...
I'm working on another new spreadsheeet. I'm sure I'll have still more
questions. But as I think through how it will work, I thought of a
generic question about spreadsheet design. I'm sure some of you have
ideas on this.

When deciding on overall approach, testing a list of some kind, how do
you decide whether to define the list as a range and then do a "For
each rCell in rRange" loop or just start from the top and work your way
down through the list using a test for a blank cell to determine when
to stop?

Obviously, if there are blank cells in the column you're testing, you
would probably use the first (defined range) approach. But what if
there aren't blanks in the data?

Any suggestions and reasons for making that choice?
Thanks!