Macro to Define Range Name

[Phil]

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.

 

[Tom Ogilvy]

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"

 

[Dianne]

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

 

[Dave Peterson]

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.

 

[Tom Ogilvy]

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

 

[Dianne]

Tom and Dave,

I love my dynamic ranges (picked up the trick from Jan Karel back on the
old Compuserve Excel forums), but have been wondering whether I'm
putting an unnecessary strain on Excel in some way. Recalculations are
fine, so I'll leave them as is. I'm glad for the confirmation -- thanks!

 

[steve]

Just a note... I have used dynamic names for some time now and they were
working great. (Excel97 SR2)

But recently 2 new projects (with forms and stuff) started crashing. I
removed the Offset formulas from the names and had VB redefine the
names as needed. The crashing ceased...

Any ideas???