Excel VBA how do I Rename a Named Range

[Roncondor]

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

 

[FormR]

Give this a try on a copy of your workbook:

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]

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.

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]

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]

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]

1. Yes Please
2. You are correct yours is preferable since it just renames them and does not require deleting

Ron

 

[Roncondor]

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]

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!