How to change range reference with VBA

Question

I have about 300 named ranges that are referring to an external spreadsheet.

for example

Range name: my_range

Refers to: ='\mycompany.com\lucas[Lucas.xlsm]SHEETNAME'!$C$10

I want to replace the "\mycompany.com\lucas[Lucas.xlsm]" with an empty string

I tried researching this online but it doesn't seem like I'm able to phrase it correctly, all the answers are referring to find and replace in cells...

[Using this as a guide](https://www.thespreadsheetguru.com/blog/the-vba-guide-to-named-ranges).... Loop through named ranges, check for existence of string in name, if it's there delete the named range and add it back without the string (using replace function). — Scott Holtzman, Feb 08 '18 at 17:12
deleting the range will break the formulas currently using it no? — LucasSeveryn, Feb 08 '18 at 17:15
Technically, it will break the formula, but not in a way that matters, since you will add the name range back on the next code line. — Scott Holtzman, Feb 08 '18 at 17:18

score 1 · Answer 1 · answered Feb 08 '18 at 17:33

There are a number of resources for doing this in VBA (300+ is a lot to do by hand!).

A great general guide is here: The SpreadsheetGuru's guide to Named Ranges in VBA

To loop through all named ranges and all named ranges in a specific worksheet:

Sub NamedRange_Loop()
'PURPOSE: Delete all Named Ranges in the Active Workbook
'SOURCE: www.TheSpreadsheetGuru.com

Dim nm As Name

'Loop through each named range in workbook
  For Each nm In ActiveWorkbook.Names
    Debug.Print nm.Name, nm.RefersTo
  Next nm

'Loop through each named range scoped to a specific worksheet
  For Each nm In Worksheets("Sheet1").Names
    Debug.Print nm.Name, nm.RefersTo
  Next nm

End Sub

To change the link, instead of using Debug.Print, edit the RefersTo. I can't find a way to directly edit the link, all the documentation suggest that you'd have to delete the link and recreate it with a new reference.

Deleting is easy - nm.Delete

Creating is easy:

'For Workbook level links 
ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell
'For Worksheet level links
Worksheets("Sheet1").Names.Add Name:=RangeName, RefersTo:=cell

See also:

Names Object
Names.Add Method
Defining and using names in VBA formulas Looping through all named ranges in excel VBA in current active sheet

score 0 · Answer 2 · answered Feb 08 '18 at 17:45

If you replace the referring address of a named range with an empty string, Excel deletes the named range. And this is the way I am using to delete a named range:

Public Sub DeleteName(sName As String)

    On Error GoTo DeleteName_Error
    ActiveWorkbook.Names(sName).Delete
    Debug.Print sName & " is deleted!"

    On Error GoTo 0
    Exit Sub

DeleteName_Error:
    Debug.Print sName & " not present or some error"
    On Error GoTo 0
End Sub

Simply call it like this:

DeleteName my_range

Actually, the deletion of the named range without .RefersTo is quite clever by the Excel developers - otherwise plenty of errors would appear. Check it out, this code would run only once if you declare my_range1 and my_range2:

Public Sub TestMe()

    Dim nameArray As Variant
    nameArray = Array("my_range1", "my_range2")
    Dim myNameRange As Name
    For Each myNameRange In ThisWorkbook.Names
        Dim cnt As Long
        For cnt = LBound(nameArray) To UBound(nameArray)
            If nameArray(cnt) = myNameRange.Name Then
                Debug.Print myNameRange
                Debug.Print myNameRange.RefersTo
                myNameRange.RefersTo = vbNullString
            End If
        Next cnt
    Next myNameRange
End Sub

The OP doesn't want to replace it with an empty string, he wishes to remove the file reference from the link, leaving only the sheey & cell reference, — Alan, Feb 09 '18 at 10:58

score -1 · Answer 3 · answered Feb 08 '18 at 17:11

You should be able to do that from the Data, Edit Links dialog. Select the link in question, click change source and point it to the workbook itself.

If that fails, download my FlexFind tool (http://jkp-ads.com/officemarketplaceff-en.asp), run it and make sure you check the Objects checkbox on the dialog.

How to change range reference with VBA

3 Answers3