I am creating an Excel program using many dynamic and static range names that
I want to use in multiple applications.

Is there any way to change the actual NAME of a range dynamically, such as
referencing a cell. For example, can I define cells A2:A4 to be named the
value in A1?

A1: Fruit
A2: Apple --
A3: Pear | - Range named Fruit ($A$1)
A4: Orange --

A1: Vegatables
A2: Tomato --
A3: Onion | - Range named Vegetable ($A$1)
A4: Pepper --

I'd like to do this without the user having to go into the Insert-Name Menu.

Sub namerangea()
Range("a1:a4").Name = Range("a1").Value
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Arun" wrote in message
...
I am creating an Excel program using many dynamic and static range names
that
I want to use in multiple applications.

Is there any way to change the actual NAME of a range dynamically, such as
referencing a cell. For example, can I define cells A2:A4 to be named the
value in A1?

A1: Fruit
A2: Apple --
A3: Pear | - Range named Fruit ($A$1)
A4: Orange --

A1: Vegatables
A2: Tomato --
A3: Onion | - Range named Vegetable ($A$1)
A4: Pepper --

I'd like to do this without the user having to go into the Insert-Name
Menu.

Or this ONE liner to make it dynamic
Range("j1").Resize(Application.CountA(Columns("j") , 0)).Name =
Range("J1").Value


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Arun" wrote in message
...
I am creating an Excel program using many dynamic and static range names
that
I want to use in multiple applications.

Is there any way to change the actual NAME of a range dynamically, such as
referencing a cell. For example, can I define cells A2:A4 to be named the
value in A1?

A1: Fruit
A2: Apple --
A3: Pear | - Range named Fruit ($A$1)
A4: Orange --

A1: Vegatables
A2: Tomato --
A3: Onion | - Range named Vegetable ($A$1)
A4: Pepper --

I'd like to do this without the user having to go into the Insert-Name
Menu.

Select A1:A4, then Ctl-Shift-F3, and accept the default (Top Row).

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Arun" wrote in message
...
I am creating an Excel program using many dynamic and static range names
that
I want to use in multiple applications.

Is there any way to change the actual NAME of a range dynamically, such as
referencing a cell. For example, can I define cells A2:A4 to be named the
value in A1?

A1: Fruit
A2: Apple --
A3: Pear | - Range named Fruit ($A$1)
A4: Orange --

A1: Vegatables
A2: Tomato --
A3: Onion | - Range named Vegetable ($A$1)
A4: Pepper --

I'd like to do this without the user having to go into the Insert-Name
Menu.

It works if the range is not already defined, but it does not rename or
create a new range if there is already an existing range named for that field.

"Don Guillett" wrote:

Sub namerangea()
Range("a1:a4").Name = Range("a1").Value
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Arun" wrote in message
...
I am creating an Excel program using many dynamic and static range names
that
I want to use in multiple applications.

Is there any way to change the actual NAME of a range dynamically, such as
referencing a cell. For example, can I define cells A2:A4 to be named the
value in A1?

A1: Fruit
A2: Apple --
A3: Pear | - Range named Fruit ($A$1)
A4: Orange --

A1: Vegatables
A2: Tomato --
A3: Onion | - Range named Vegetable ($A$1)
A4: Pepper --

I'd like to do this without the user having to go into the Insert-Name
Menu.