Delete defined names Scoped to Workbook[external]

[nagovind]

Dear All,

Kindly advise the code that will delete the named ranges that are external to the workbook
Thank you

 

[Chihiro]

You are looking at wrong property. Scope is context in which Named Range can be referred to (i.e. Worksheet scope named range only works when referenced in specific sheet etc.).

What you want to do is check Refers to: string and check for specific character.

Ex: Since external workbook name is always bounded by "[]".

Sub Demo()
Dim wName As Name

For Each wName In ThisWorkbook.Names
    If InStr(wName.RefersTo, "[") Then
        wName.Delete
    End If
Next
End Sub

 

[nagovind]

Dear Chihiro,
Thank you very much for your reply.
The code is working well and deleted the external reference but few external references as shown below is left undeleted
Kindly advise

 

[nagovind]

 

[Chihiro]

Those are not standard named range, but generated by setting print properties I believe. Try checking for namedrange.name and if it starts with "Print_" or some other condition, delete it.

If you need further help, upload sample workbook so I can run tests on it.

 

[nagovind]

Dear Chihiro,
Thank you very much for your reply.
I have attached a file wherein few named ranges are still left that is referring to external workbook even after running the code
Please help to resolve the same
Thank you

 

[Chihiro]

Something is very odd about those "Print_..." named ranges.

1. Scope for Print named ranges should never be "workbook" as they are always "Worksheet" level named ranges.
2. No code is able to delete these named range (outside of using .SendKeys). Likely due to scope being out of scope.
3. Odd thing is, while code is unable to delete, there isn't any error thrown either.

You'll need to manually delete these.

 

[nagovind]

Dear Chihiro,
Thank you for your support and efforts. Ok will delete manually
Kind Regards