1

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.

1
  • If ... Then Range(nr).ClearContents? Or .Delete if you want to remove the cells/rows
    – BruceWayne
    Jul 21, 2017 at 17:40

1 Answer 1

2

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
1
  • Nice answer, thank you and welcome to Superuser
    – wizlog
    Jul 25, 2017 at 17:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.