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.

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"


--
Regards,
Tom Ogilvy

"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.

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.

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

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

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!

--
Dianne

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


"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