Modifying the Range Nameange the names

[Arun]

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.

 

[Don Guillett]

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

 

[Don Guillett]

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

 

[Bob Phillips]

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]

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.