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!

I will almost always use a for each next loop for the following reasons...

1. It is faster. The code does not have to test for the stop condition.
2. I have more alternatives in terms of defining the range I want to search.
Using named ranges and .SpecialCells and .End and .Offset I can define
exactly where I want to look very efficiently.

The only time I don't do this is if I am deleteing or such and my search
range is changing during the execution.
--
HTH...

Jim Thomlinson


"davegb" wrote:

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!

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

(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!

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!

Bob
I was reading about your help with ranges, and I thought I would pose this
question:

Is their away to dynmically set a range based off of 1 column in a pivot
table such as column -'D7:D(PivotEndCell) the pivot changes with every run
of the report?


--
Wendell A. Clark, BS
-------------------------------------

CONFIDENTIALITY NOTICE: This e-mail communication and any attachments may
contain confidential and privileged information for the use of the
designated recipients named above. If you are not the intended recipient,
please notify us by reply e-mail. You are hereby notified that you have
received this communication in error and that any review, disclosure,
dissemination, distribution or copying of it or its contents is prohibited.
If you have received this communication in error, please destroy all copies
of this communication and any attachments. Contact the sender if it
continues.


"Bob Phillips" wrote in message
...
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

(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!