Excel VBA how do I Rename a Named Range

Roncondor

Board Regular
Joined
Jun 3, 2011
Messages
67
I have a number of range names that I would like to automatically rename

I have;
a range containing the list of the old names
a range containing the list of the desired new names

I started something along the lines of the below, but can not get it to work.

Thank you!

First and Best answer gets $25 donation to non-denominational charity of your choice (if approved)

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

Sub Rename_names()

Dim N As Name
Dim X As Integer

Const Range_from = "oldnamed_Range"
Const Range_toto = "newnamed_Range"

Dim oldname As String
Dim newname As String

For X = 1 To Range(Range_from).Rows.Count

oldname = Range(Range_from).Cells(X).Value
newname = Range(Range_toto).Cells(X).Value

N = oldname ‘ PROBLEM IS THAT NAME DOES NOT LIKE A
N.RefersToLocal

‘Need to 1. assign new name to old refers to
2. replace all references to oldname in spreadsheet with newname
N.Delete

Next X

Exit sub
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,480
Office Version
  1. 365
Platform
  1. Windows
Give this a try on a copy of your workbook:

Code:
Sub Macro1()
Dim i As Long
For i = 1 To Range("oldnamed_Range").Rows.Count
    With ThisWorkbook.Names(Range("oldnamed_Range").Cells(i, 1).Value)
        .Name = Range("newnamed_Range").Cells(i, 1).Value
    End With
Next i
End Sub
 

Cool blue

Board Regular
Joined
Dec 1, 2013
Messages
199
This one also cleans up the old names.
Instead of using two ranges and assuming they are aligned, I suggest you just have a named range for the old names and put the new names one cell to the right.

I assume that all of the names are Workbook scope and that it applies to the ActiveWorkbook.

Code:
Sub Rename_names()
Const Range_from As String = "oldnamed_Range"
'Assumes you put the new names in the next cell to the right of the old name
Dim N As Name
Dim c As Range

For Each c In Range(Range_from)
    With ActiveWorkbook
        .Names.Add Name:=c.Offset(0, 1).Value, RefersToLocal:=.Names(c.Value).RefersToLocal
        .Names(c.Value).Delete
    End With
Next c

End Sub
 

Roncondor

Board Regular
Joined
Jun 3, 2011
Messages
67
That works! This is how I did it:
=======================

Sub Rename_names()


Dim X As Integer


Const Range_from = "oldnamed_Range"
Const Range_toto = "newnamed_Range"


Dim oldname As String
Dim newname As String


For X = 1 To Range(Range_from).Rows.Count


oldname = Range(Range_from).Cells(X).Value
newname = Range(Range_toto).Cells(X).Value

With ThisWorkbook.Names(oldname)
.Name = newname
End With

Next X


End Sub
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,480
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

That works!

Cool! Do I get to nominate a charity ;)

This one also cleans up the old names

Hi Cool blue, the code I suggested and that Roncondor adapted renames the existing named ranges; so there are no old names to clean up - this has the added advantage of automatically updating any formulas that reference those named ranges.
 

Roncondor

Board Regular
Joined
Jun 3, 2011
Messages
67
1. Yes Please
2. You are correct yours is preferable since it just renames them and does not require deleting

Ron
 

Roncondor

Board Regular
Joined
Jun 3, 2011
Messages
67
Thank you. The American Red Cross is very grateful for your generous gift of $25.00 on January 20, 2014 to Disaster Relief
 

Cool blue

Board Regular
Joined
Dec 1, 2013
Messages
199
Cool! Do I get to nominate a charity ;)



Hi Cool blue, the code I suggested and that Roncondor adapted renames the existing named ranges; so there are no old names to clean up - this has the added advantage of automatically updating any formulas that reference those named ranges.

Yep, I see what you mean: very nice!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,328
Messages
5,624,036
Members
416,007
Latest member
csf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top