Renaming ranges in VBA

Recently a question came up on an Excel forum. The request was to rename hundreds of named ranges in VBA. The simplest option is to create an array from a 2-column table and loop through the array, making changes to each name in the first column. The table is called NameList. Populate and name the table (old names in the first column), paste this code into a new module, and run it.

Note: If you haven’t created a named range before,  follow the steps below.

1. Select the data (not the headings), highlighted here in blue

table

2. Click the Name Box (just to the left of the formula bar), type the name (no gaps) and press Enter.

named_range
Sub NameChanger()
    Dim arNames()
    Dim nm As Name
    Dim i As Integer

    arNames = 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

You can confirm the names changes by pressing F3 and then Alt+L (on a blank sheet).
Optional extra: If you want to check the range references (and list them next to the NameList table) paste this to your module and run it:

Sub NameRefs()
    Dim c As Range

    For Each c In WorksheetFunction.Index(Range("NameList"), 0, 2)
        c.Offset(0, 1) = "'" & Names(c.Value).RefersTo
    Next c
End Sub

About sydneygeek

I was a consultant in Excel and Access for about 14 years before taking a full time role as an analyst with an energy company. Tech interests include Excel, Access, VBA, HTML / CSS and Oracle. I am based in Sydney, Australia and am a moderator on MrExcel, where I go by the name SydneyGeek.
Bookmark the permalink.