vba to clear out stuff in Name Manager

Is there a way to clear anything that is in Name manager. When my macro opens various files, sometimes there is something in Name Manager that links to other files. I do not want this included in my new file and would like to delete everything out of Name Manager. If I use something specific like ActiveWorkbook.Names("Diff_Type").Delete and "Diff Type" is not in that particular file Macro falls over.

So is there a way to Clear Names Manager no matter what is in it?
JagwarmanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
try:

Sub test()
    Dim n As Name
    For Each n In Names
        n.Delete
    Next
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roy CoxGroup Finance ManagerCommented:
That approach may clear Names that you need.

You can clear names with a REF error like this
Sub Delete_Ref_Names()
Dim nName As Name
    For Each nName In Names
        If InStr(1, nName.RefersTo, "#REF!") > 0 Then
            nName.Delete
        End If
    Next nName   
End Sub

Open in new window


The following code I believe was written by Aaron Bloog and will remove all "bad" names

Option Explicit

Sub RemoveDemonLinks()
    Dim wbBook As Workbook
    Dim nName  As Name
    Dim i      As Long

    Set  wbBook = ActiveWorkbook

    i = 0
    If wbBook.Names.Count > 0 Then
        With Application
            .ReferenceStyle = xlR1C1
            .ReferenceStyle = xlA1
        End With

        For Each nName In wbBook.Name
            If InStr(nName.RefersTo, "#REF!") > 0 Then nName.Delete
            i = i + 1
        Next nName

        If i > 0 Then MsgBox i & " corrupted names was deleted from " & wbBook.Name
    End If

End Sub

Open in new window

JagwarmanAuthor Commented:
Both excellent thanks
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Roy CoxGroup Finance ManagerCommented:
Glad we helped
JagwarmanAuthor Commented:
problem has arisen with this. I am now getting an error

The name that you entered is not valid.
 Reasons for this can include:
 -The name does not begin with a letter or an underscore
 -The name contains a space or other invalid characters
 -The name conflicts with an Excel built-in name or the name of another object in the workbook.

and when I try the one written by written by Aaron Bloog  I get Compile error For each may only iterate over a collection object or an array.

Please help
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
since this question was closed, do you think it's better if you open another question for this issue?
Roy CoxGroup Finance ManagerCommented:
You probably need to add an error handler.
JagwarmanAuthor Commented:
Thanks Roy
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.