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
2. Click the Name Box (just to the left of the formula bar), type the name (no gaps) and press Enter.
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