I support the use of dynamic named ranges. Unless you find recalculations
are taking a long time, I wouldn't worry about them.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
I don't know about how much overhead for the dynamic range names (but I
don't
care either--my time is more important than a few extra megs of
memory/storage). I'll let excel be excel.

My feeling is that if I'm building the pivottable (for example) from
scratch
each time I need it, I'll do it in code. (Like if I'm importing a text
file and
doing summaries.) But if I know that the data/pivottable will be
refreshed
manually, I'll use the dynamic ranges.



Dianne wrote:

Tom,

If I can jump in here with a question I've been curious about for a
while now --

I have a couple of applications in which I use three or four dynamic
ranges (using OFFSET). These ranges form the basis for various filtering
operations, pivot tables, etc.

Is there a lot of overhead involved in these dynamic ranges? Are there
advantages to setting the range in code as in your code below? Or
disadvantages?

One disadvantage that immediately springs to mind is that the range
needs to be reset every time data is added to the table, but the dynamic
range automatically copes. I could get round this fairly easily, if I
had a reason to. Do I?

Thanks

--
Dianne

In ,
Tom Ogilvy typed:
If the range is separated from everything else by at least one blank
row top and bottom and one blank column left and right, you can do

Range("MyName").CurrentRegion.Name = "MyName"

Or, for a single column Range

Dim rng as Range
set rng = Range("MyName")

Range(rng(1),rng(1).End(xldown)).Name = "MyName"



"Phil" wrote in message
...
Can anyone suggest how to create a macro to define and
update a range name in a worksheet each time the data
inreases or decreases? My range changes from time to time
and each time I have to manually define the range.

Thanks in advance.

--

Dave Peterson