VBA Code to Rename a Defined Range

[azbasketcat]

I am trying to find a way to use VBA to change the name of a Defined Range.

Everything I have found so far creates a new range and then deletes the old range. I have used the existing named ranges throughout a large workbook, so I don't want to delete the existing named ranges.

Also, I have the names of the ranges I want to rename (a subset of all the named ranges in the workbook) in a range called "ExistingNames" and then I have another named range called "NewNames" which has the new name. My hope is to have the macro loop through ExistingNames and for each range assign the new name.

Any suggestions would be greatly appreciated. Thanks

 

[Sal Paradise]

There is no renaming for named ranges. You could create a new name for each name, replace the old name with the new name throughout the workbook, and then delete the old names.

That's probably the best you'll be able to do.

 

[JoeMo]

If you have an existing range named "cat", you can name the same range "dog", but unless you delete the name "cat" you will end up with two names for the same range - i.e. cat & dog will have the same scope and same refers To address. If you can live with the duality, this isn't a fundamental problem b/c:
=cat+5 will return the same value as
=dog+5

 

[SydneyGeek]

This worked for me...
1. Create a 2-column list with existing name on the left, new name on the right. Name it NameList.
2. Copy this code to a new module. You will need to change the sheet reference for NameList: I created the range on Sheet2.

Sub NameChanger()
    Dim arNames()
    Dim nm As Name
    Dim i As Integer
    
    arNames = Sheets("Sheet2").Range("NameList").Value
    For i = LBound(arNames) To UBound(arNames)
        For Each nm In ActiveWorkbook.Names
            If nm.Name = arNames(i, 1) Then
                nm.Name = arNames(i, 2)
            End If
        Next nm
    Next i
End Sub

3. Run the code. Your old range names will be replaced with the new ones.

Denis

 

[azbasketcat]

Thank you both for your quick responses. I appreciate the help.

 

[azbasketcat]

Denis - I searched all over the Internet for this and couldn't find an answer and your solution worked perfectly.

Thank you!! Randy

 

[SydneyGeek]

You're welcome

Denis

 

[Sal Paradise]

Wow, I had no idea you could do that. I had been trying to do that for the past 8 months or so, and had totally given up.

Wow.

Thanks for that! I feel bad for saying it couldn't be done, boy I feel dumb.

 

[Jerry Sullivan]

Hi all,

Building on Denis' example, the objects could be referenced directly by index name instead of iterating through the Names collection. This would be more efficient, although the difference would not be noticeable unless you have a large number of names.

Sub NameChanger2()
    Dim arNames()
    Dim i As Integer
    
    arNames = Sheets("Sheet2").Range("NameList").Value
    For i = LBound(arNames) To UBound(arNames)
        If NameExists(arNames(i, 1)) Then _
            ActiveWorkbook.Names(arNames(i, 1)).Name = arNames(i, 2)
    Next i
End Sub

Public Function NameExists(ByVal sName As String) As Boolean
    On Error Resume Next
    NameExists = ActiveWorkbook.Names(sName).Name = sName
End Function

An enhancement to consider would be to provide the user with a message if an exception occurs that prevents the rename.

Sub NameChanger3()
    Dim arNames()
    Dim i As Integer
    Dim sOld As String, sNew As String
    
    arNames = Sheets("Sheet2").Range("NameList").Value
    For i = LBound(arNames) To UBound(arNames)
        sOld = arNames(i, 1)
        sNew = arNames(i, 2)
        If NameExists(sOld) Then
            If Not (NameExists(sNew)) Then
                With ActiveWorkbook.Names(sOld)
                    .Name = sNew
                    If .Name <> sNew Then
                        MsgBox "Unable to rename " & sOld & " to " & sNew & "." & vbCr _
                            & "Make sure to use valid names."
                    End If
                End With
            Else
                MsgBox "New name: " & sNew & " already exists"
            End If
        Else
            MsgBox "Old name: " & sOld & " not found"
        End If
    Next i
End Sub

 

[JoeMo]

This worked for me...
1. Create a 2-column list with existing name on the left, new name on the right. Name it NameList.
2. Copy this code to a new module. You will need to change the sheet reference for NameList: I created the range on Sheet2.

Sub NameChanger()
    Dim arNames()
    Dim nm As Name
    Dim i As Integer
    
    arNames = Sheets("Sheet2").Range("NameList").Value
    For i = LBound(arNames) To UBound(arNames)
        For Each nm In ActiveWorkbook.Names
            If nm.Name = arNames(i, 1) Then
                nm.Name = arNames(i, 2)
            End If
        Next nm
    Next i
End Sub

3. Run the code. Your old range names will be replaced with the new ones.

Denis

Denis,
I couldn't get this to work for me. I did the following:
1. Created a new workbook with 2 sheets - Sheet1 & Sheet2
2. Named A1 on Sheet1 "cat"
3. Named A4:B4 on Sheet2 "NameList"
4. Entered "cat" in Sheet2 A4, "dog" in Sheet2 B4
5. Ran the code and went to the Name Manager. Only two names (scoped at workbook level) "cat" and "NameList" appeared.

I then stepped through the code and verified that arNames had the right content (arNames(1,1) = "cat", arNames(1,2) = "dog") and that each step executed properly. Still "dog" did not appear in the Name Manager and "cat" persisted.

What am I doing wrong?