Excel 2010 VBA - Delete Names With No Reference to Workbook

Hello Experts,

I'm in need of your help in modifying a vba code that will delete any names in a workbook that meet the following criteria:

1.

The "Refers to" is currently #REF! - For this step, I already have the code that works perfectly, see below
Sub DeleteRefErrRanges()

    Dim nName As Name
    Dim iRes As Integer
    Dim strPrompt, strTitle As String
    
    strPrompt = "This will delete ALL named ranges with reference error #REF!" & vbNewLine & vbNewLine & "Are you sure?"
    strTitle = "Caution"
    
    iRes = MsgBox(strPrompt, vbYesNo + vbCritical, strTitle)
    
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
    
    If iRes = vbYes Then
        For Each nName In ActiveWorkbook.Names
            If InStr(1, nName.RefersTo, "#REF!") > 0 Then
                nName.Delete
            End If
        Next nName
        MsgBox "Done!"
    Else
        Exit Sub
    End If
    
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
End Sub

Open in new window

2.

In addition to the Step 1 above, I would also like the code to delete any names that are no longer used.  I'm thinking that the code could scan names that have a reference to any worksheet in the workbook, leave these intact and delete all other names that refer to a hard-coded amounts, external files, url links, etc.
Any help with this will be greatly appreciated.

Thank you,


Sura
SuraDalbinAccountantAsked:
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.

ProfessorJimJamMicrosoft Excel ExpertCommented:
try this


Sub DeleteRefErr() 
     
    Dim nm As Name 
     
    For Each nm In ActiveWorkbook.Names 
        If InStr(1, nm.RefersTo, "#REF!") > 0 Then nm.Delete 
    Next nm 
End Sub 

Open in new window

0
SuraDalbinAccountantAuthor Commented:
Hello ProfessoJimJam,

Thank you for your response, but this code seems to be exactly the one I posted, which already works as expected.  I would just need help in adding that extra step, which is Step 2 in my original question.

Thanks again.
0
ProfessorJimJamMicrosoft Excel ExpertCommented:
Ok


the VBA on this aricle is what you need.

i dont copy and paste the VBA which i did not write myself, therefore referring you to the original site http://excelribbon.tips.net/T010998_Finding_Unused_Names.html
0
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

ProfessorJimJamMicrosoft Excel ExpertCommented:
here you can try this one and it will delete all named ranges with external reference and also the one with error.

Sub DeleteRefErr()
     
    Dim nm As Name
     
    For Each nm In ActiveWorkbook.Names
        If InStr(1, nm.RefersTo, "#REF!") > 0 Then nm.Delete
    Next nm
For Each nm In ActiveWorkbook.Names
If nm.Name Like "ExternalData*" Then nm.Delete

Next nm
End Sub

Open in new window

0
Saurabh Singh TeotiaCommented:
Sura,

You can try the following code it will do what you are looking for..I have made following assumptions to tackle what you asked...

1. Hard code values--> It doesn't have sheet reference in it which generally has sign-->! in it
2. External workbook reference--> It has generally has this sign in it-->[
3. External link reference---> this has http in it or .com .. Please change basis of what you see...

Sub DeleteRefErrRanges()

    Dim nName As Name
    Dim iRes As Integer
    Dim strPrompt, strTitle As String
    
    strPrompt = "This will delete ALL named ranges with reference error #REF!" & vbNewLine & vbNewLine & "Are you sure?"
    strTitle = "Caution"
    
    iRes = MsgBox(strPrompt, vbYesNo + vbCritical, strTitle)
    
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
    
    If iRes = vbYes Then
        For Each nName In ActiveWorkbook.Names
            If InStr(1, nName.RefersTo, "#REF!") > 0 Or InStr(1, nName.RefersTo, "!") = 0 Or InStr(1, nName.RefersTo, "[") > 0 Or InStr(1, nName.RefersTo, "http", vbTextCompare) > 0 Then
                nName.Delete
            End If
        Next nName
        MsgBox "Done!"
    Else
        Exit Sub
    End If
    
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
End Sub

Open in new window


Saurabh...
0
SuraDalbinAccountantAuthor Commented:
Hello Saurabh,

Thank you very much for your response.  I like this approach, and it seems to be working.  I modified the following line:
If InStr(1, nName.RefersTo, "#REF!") > 0 Or InStr(1, nName.RefersTo, "!") = 0 Or InStr(1, nName.RefersTo, "[") > 0 Or InStr(1, nName.RefersTo, "http", vbTextCompare) > 0 Then

Open in new window

For
If InStr(1, nName.RefersTo, "#REF!") > 0 Or InStr(1, nName.RefersTo, "\") > 0 Or InStr(1, nName.RefersTo, "[") > 0 Or InStr(1, nName.RefersTo, "http", vbTextCompare) > 0 Then

Open in new window


Because I kept getting a runtime error message.

Now, I realize that since I haven't provided a sample of the names that I have encountered, it's a bit hard for you to anticipate or what to have an "OR" statement for.

I can definitely work with this code, but I was wondering if you'd know of a way to say:
InStr(1, nName.RefersTo, "'Name of NonExistentSheet!'") <> the name of any worksheet in ActiveWorkbook

Open in new window


Where the code will take the name of the Non Existent sheet (which is inside single quotes, for the most part) and compare to all worksheets in active workbook.

Thanks again.
0
SuraDalbinAccountantAuthor Commented:
ProfessorJimJam,

I tried both of your suggestions, thank you.  Maybe the only drawback for the first code you suggested, the link you provided, is that it searches for the names in the current sheet only, and if not found, then it deletes.

As far as your second code, it seems that the second part to the code:
For Each nm In ActiveWorkbook.Names
If nm.Name Like "ExternalData*" Then nm.Delete

Open in new window


does not do anything when I run it.

Thank you for your responses and help.
0
Saurabh Singh TeotiaCommented:
Sura,

Quick question..if the sheet is not their in the active workbook..then won't name has a sign of external data which is this [ or it will be ref range.. Apart then these two i don't see how that range is displayed.. If you can give me a screenshot of it's reference..i can probably look into it and help you for the same. Also you said it gave you a ref error the code should not give you an error because the ! sign i posted is for hard code values since they won't have sheet name in the same..

Saurabh...
0
SuraDalbinAccountantAuthor Commented:
Saurabh,

Yes, please see screenshot attached.  Basically these names don't exist in this workbook because these were brought along when a worksheet from another workbook was inserted into the workbook I'm working on.
 Sample of Named Ranges
Thanks,

Sura
0
Saurabh Singh TeotiaCommented:
Sura,

Are you sure about that?? Because it's surprising for me..Have you checked for any hidden sheets in the workbook?? Which is in Home-->Format-->unhide sheet...

or you can even run a simple code which make all the worksheets visible..Please confirm and if the sheets are not their..accordingly then will design a solution...

dim ws as worksheet

for each ws in activeworkbook.worksheets
ws.visible=true
next ws

Open in new window

0
SuraDalbinAccountantAuthor Commented:
Yes, the sheets listed in that screenshot I sent you are not part of this workbook.  I just double-checked and also, there no hidden sheets in this workbook.
0
Saurabh Singh TeotiaCommented:
Sura,

You can do something like this to delete all the ranges which are not referring to worksheets which are present in your current workbook...

Sub checkmyrange()
    Dim nm As Name

    For Each nm In ActiveWorkbook.Names

        If chcknme(nm.RefersTo) Then nm.Delete
    Next nm

End Sub

Function chcknme(r As String) As Boolean

    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        If InStr(1, r, ws.Name, vbTextCompare) > 0 Then
            chcknme = False
            Exit Function
        End If
    Next ws

    chcknme = True
End Function

Open in new window


Saurabh...
0
SuraDalbinAccountantAuthor Commented:
Hi Saurabh,

I tested the code, but I get a run-time error, see attached

Capture-3.PNG
I'm attaching a copy of the workbook I am working on, for you to test.  All tabs have been removed and the only useful name in this workbook is "TB_CY".  All others should be deleted.

THank you very much for all your help.
Sample-2---Copy.xlsx
0
Saurabh Singh TeotiaCommented:
Not sure what you are doing...if i run this code it works perfectly for me and clear your range...

Sub checkmyrange()
    Dim nm As Name

    For Each nm In ActiveWorkbook.Names

        If chcknme(nm.RefersTo) Or InStr(1, nm.RefersTo, "#REF!") > 0 Then nm.Delete
    Next nm

End Sub

Function chcknme(r As String) As Boolean

    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        If InStr(1, r, ws.Name, vbTextCompare) > 0 Then
            chcknme = False
            Exit Function
        End If
    Next ws

    chcknme = True
End Function

Open in new window


Your workbook for reference...

Saurabh...
Sample-2---Copy.xlsm
0

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
ProfessorJimJamMicrosoft Excel ExpertCommented:
i agree with Saurbh.  i run the code and it worked for me too.

the only irrelevant named range , the code could not delete was the Print_Area  refer to two REF errors.
0
SuraDalbinAccountantAuthor Commented:
Saurabh,

I apologize for the delay, I had a super busy day at the office.  I made some modifications to the code and have tested it thoroughly and it seems to be working like a charm.  See updated code:

Sub DeleteRefErrRanges()

    Dim nm As Name
    Dim iRes As Integer
    Dim strPrompt, strTitle As String
    
    strPrompt = "This will delete ALL unused named ranges, including #REF ranges!" & vbNewLine & vbNewLine & "Are you sure?"
    strTitle = "Caution"
    
    iRes = MsgBox(strPrompt, vbYesNo + vbCritical, strTitle)
    
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
    
    On Error Resume Next
    If iRes = vbYes Then
        For Each nm In ActiveWorkbook.Names
            If InStr(1, nm.RefersTo, "#REF!") > 0 Or InStr(1, nm.RefersTo, "#REF!#REF!") > 0 Or InStr(1, nm.RefersTo, "#N/A") > 0 Or InStr(1, nm.RefersTo, "\") > 0 Or InStr(1, nm.RefersTo, "[") > 0 Or InStr(1, nm.RefersTo, "http", vbTextCompare) > 0 Then
                nm.Delete
            End If
        Next nm
        For Each nm In ActiveWorkbook.Names
            If chcknme(nm.RefersTo) Then nm.Delete
        Next nm
        MsgBox "Done!"
    Else
            Exit Sub
    End If
    On Error GoTo 0
    
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
End Sub

Function chcknme(r As String) As Boolean

    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        If InStr(1, r, ws.Name, vbTextCompare) > 0 Then
            chcknme = False
            Exit Function
        End If
    Next ws

    chcknme = True
End Function

Open in new window


As you can see, I basically combined the two examples you provided and I then added a error handler.

Thank you very much for your help.
0
SuraDalbinAccountantAuthor Commented:
ProfessorJimJam,

Thank you very much for your help as well.
0
SuraDalbinAccountantAuthor Commented:
Thank you for your help!
0
Saurabh Singh TeotiaCommented:
Sura..

Yw... Happy to Help.. :-)

Saurabh...
0
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
Microsoft Excel

From novice to tech pro — start learning today.