Can VBA delete named ranges based on RefersTo value?

Question

I'm cleaning up a bloated Excel workbook and am trying to bulk delete named ranges that are referring to workbooks on someones 'C' drive. There are a lot of them. And they are not being used in this Workbook.

'C:\blahblah\...\...\...\...\...\[WorkBookName.xls]Sheet1'!$E$28

The code below helped me delete named ranges where their name contains 'icon' (for example).

However, I really need to delete the range when it contains a path as shown above. Bearing in mind that the Workbook or Sheet name could also change, but the drive remains the same.

Sub deleteNamedRangesSpecific()
For Each NR In ActiveWorkbook.Names
    If NR.NameLocal Like "*" & "icon" & "*" Then NR.delete
Next
End Sub

Any ideas how to delete according to the range itself, not the name of the range?

Thanks.

Mike.

Answer 1

You just need to pass the NR.RefersTo variant to a string type variable so you can use the Like function.

This code works for me. Obviously edit $E$28 to suit.

Option Explicit
Sub deleteNamedRangesSpecific()
    Dim NR As Name
    Dim compare_string As String
    For Each NR In ActiveWorkbook.Names
        compare_string = NR.RefersTo
        If compare_string Like "*" & "$E$28" & "*" Then NR.Delete
    Next
End Sub