asked on

Excel 2007 VBA - code to change range name with Old and New names entered in cells

I have two cells named "c_OldName" and "c_NewName"
I want to enter an existing range name (text string) into cell "c_OldName" and use vba to change the name to whatever text string is in cell "c_NewName"

For example, if there exists a named range called "RangeOne", I want to be able to enter the text string "RangeOne" into cell "c_OldName", and enter text string "RangeNewName" into cell "c_NewName, then run the code and have the range name change from "RangeOne" to "RangeOneNewName"

Thanks, --Andres

StephenJR

Andres - this perhaps?

Range(Range("c_OldName").Value).Name = Range("c_NewName").Value
Range(Range("c_OldName")).Name.Delete

AndresHernando

ASKER

Stephen,
Thanks for your help with this. Your solution is almost there...
The first line succeeds in changing the name correctly, but the 2nd line actually deletes the new name rather than the old one (!)

StephenJR

Andres - if I name A1 "c_OldName" and put "RangeOne" in it, name A2 "c_OldName" and put "RangeOneXXX" in it, and name A3:A7 "RangeOne" and run the macro A3:A7 is renamed "RangeOneXXX" and "RangeOne" is deleted.

ASKER CERTIFIED SOLUTION

StephenJR

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AndresHernando

ASKER

Stephen, nicely done... Works very well.

the problem I has having was due to running it more than once with the same (obsolete) info in the input fields.

THANKS!!! --Andres