VBA to delete external named ranges (or with #REF in the named range value)

Reddog94

Board Regular
Joined
Dec 20, 2011
Messages
52
I am attempting to delete external named ranges after a sheet is copied out of a source workbook (the template). I have tried both the following codes, but neither one does the trick. The code is in ThisWorkbook (not a module), and I have tried it with the code both in the template and the new doc. The new doc is where I want the names to be deleted (there are no #REF names in the template).

Deletes named ranges based on the reference to the source doc:

Code:
Sub DelRanges()
Dim nName As Name
 
    For Each nName In Names
        If InStr(1, nName.RefersTo, "='T:\Fin_") > 0 Then
            nName.Delete
        End If
    Next nName
End Sub


Deletes named ranges based on a #REF in the value of the range (this is my preferred method):

Code:
Sub DeadRanges()
Dim nName As Name
 
    For Each nName In Names
        If InStr(1, nName.Value, "#REF") Then
            nName.Delete
        End If
    Next nName
End Sub
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Also, for the first code I have tried the following for RefersTo:

"'T:\Fin_" (excludes equal sign - which I think is proper but including it was worth a shot when I couldn't get it to work)
"T:\Fin_" (excludes equal sign and single apostrophe)
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top