Modifying the Range Nameange the names

  • Thread starter Thread starter Arun
  • Start date Start date
A

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.
 
Or this ONE liner to make it dynamic
Range("j1").Resize(Application.CountA(Columns("j"), 0)).Name =
Range("J1").Value
 
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)
 
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.
 
Back
Top