VBA rename many named ranges by address

[SF_Marnie]

Hi,

I copied a tab with a million named ranges and formulas. I’d like to rename all on the new name to be prefixed with a tab indicator while leaving the original named ranges unchanged.

Currently the name manager has duplicate entries for each name, distinguished only by address.

I found code from @SydneyGeek in 2012 here: mrexcel.com/boards/threads/vba-code-to-rename-a-defined-range.660541/ that attaches a new name list by referencing items by their existing name, which would work if my name manager didn’t have duplicates.

It seems like if I replace Dim arNames() with whatever dim references the address I could try to revise the code to take my list of locations and new names, but I don’t know how to reference that. I googled for “arNames()” thinking that might lead me somewhere that would have the reference for the “refers to” column of the Name Manager table, but no dice.

Thanks for any help! I know I can rename one-by-one, but I have to believe a VBA solution is possible.

 

[SF_Marnie]

Sorry for typos, that needed a proof read. I don’t see an edit option, so leaving as is.

 

[MARK858]

arNames in the code is the name for the Variable for the range Sheets("Sheet2").Range("NameList").Value
just create a Named Range for the range you want called NameLIst with the 2 columns (and change the sheet name to suit).
Denis does provide instructions in the post.

VBA Code to Rename a Defined Range

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...

www.mrexcel.com

 

[SF_Marnie]

arNames in the code is the name for the Variable for the range Sheets("Sheet2").Range("NameList").Value
just create a Named Range for the range you want called NameLIst with the 2 columns (and change the sheet name to suit).
Denis does provide instructions in the post.

VBA Code to Rename a Defined Range

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...

www.mrexcel.com

Hm, I’m not sure if this answered my question. I’m wondering how to reference the list of locations rather than the list of current names. I totally see how I could use the prior code if I were able to reference by the current names but it isn’t readily apparent how I can modify the code to match the new name to the named item by location.

 

[rlv01]

I think we need more information about what you are trying to do. There are two kinds of named ranges in excel- Workbook level names, and worksheet level names. It is fairly easy to code something to dump the names for inspection. Here is something I use.

'Dump workbook and worksheet level names to a worksheet
Sub NameDump()
    Dim N As Name
    Dim WB As Workbook
    Dim NameWS As Worksheet
    Dim Name As String, R As String, S As String
    Dim NArr As Variant
    Dim I As Long, NCnt As Long, BCnt As Long, WBCnt As Long, WSCnt As Long
    Dim CellRange As Range
    
    Set WB = ActiveWorkbook
    Application.ScreenUpdating = False
    
    'Delete any previous NameDump sheet
    On Error Resume Next
    Application.DisplayAlerts = False
    WB.Worksheets("NameDump").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    DoEvents

    'Add sheet
    Set NameWS = WB.Worksheets.Add(After:=WB.Worksheets(Worksheets.count))
    NameWS.Name = "NameDump"
    
    NameWS.Cells(1, 1).Value = "Name"
    NameWS.Cells(1, 2).Value = "Scope"
    NameWS.Cells(1, 3).Value = "Visble"
    NameWS.Cells(1, 4).Value = "RefersTo"
    
    NCnt = WB.Names.count
    Set CellRange = NameWS.Range("A2").Resize(NCnt, 4)
    NArr = CellRange.Value
    
    'Loop thru names
    I = 0
    For Each N In WB.Names
        If Not N.Value = "=#NAME?" Then
            I = I + 1
            Name = N.Name
            R = "'" & N.RefersTo
            NArr(I, 1) = N.Name
            NArr(I, 3) = N.Visible
            NArr(I, 4) = R
            
            If VBA.InStr(N.Name, "!") = 0 Then
                NArr(I, 2) = "Workbook"
                WBCnt = WBCnt + 1
            Else
                NArr(I, 2) = "Worksheet"
                WSCnt = WSCnt + 1
            End If
        Else
        BCnt = BCnt + 1
        End If
    Next N
    
    'Write names to worksheet
    CellRange.Value = NArr
    
    'Sort, format, and filter
    With NameWS
        .UsedRange.Sort Key1:=.Range("A1"), Header:=xlYes
        .UsedRange.Columns.AutoFilter
        .Range("A1:D1").Font.Bold = True
        .Range("A1:D1").Borders(xlEdgeBottom).Weight = xlMedium
        Range("A2").Select
        ActiveWindow.FreezePanes = True
        .Columns.AutoFit
    End With
    Application.ScreenUpdating = True
    S = "Workbook '" & WB.Name & "'" & vbCr & vbCr
    S = S & WBCnt & " Workbook level names" & vbCr
    S = S & WSCnt & " Worksheet level names" & vbCr
    MsgBox S, vbInformation, "Named Range Count"
End Sub