Over 400 hidden defined names leftover from 3rd-Party API...The names are almost identical...How can I remove w/o deleting my own defined name ranges?

[Excelerate2014]

Hello,

So Blooomberg excel add-in (which I no longer use) left a residual list of defined names on my excel files (all of them). They are named BLPH1 through BLPH400 (and counting). So when I copy worksheets I need to hold the enter key until 400+ "Yess" are confirmed. And if I change my mind. No matter, once you hit copy worksheet there is no turning back. The dialog box will not go away.

I use named ranges. All of the defined names and named ranges listed in Name manager are mine (and there are a bunch...some 100+ dynamic name ranges) so its impractical to use the VBA script that deletes all named ranges.

I am not very good with VBA, but I must imagine that given all of the hidden names are basically the same name just a different number that there is a simple way to use VBA to delete just those names with "BLPH1" to "BLPH400"?

Excel 2013

Thanks,

 

[mikerickson]

Would this work for you?

Dim oneName As Name

For Each oneName In ThisWorkbook.Names
    If Not oneName.Visible Then
        oneName.Delete
    End If
Next oneName

 

[Excelerate2014]

Thanks Mike. I am very very green when it comes to VBA. Just figuring out how to write the script to turn on Manual calc with iterations at start-up was a monumental event for me. Would you mind adding a bit more direction? Am I writing each word of that cose exactly as is? Do I use a basic module? Do i do this over 400 times? ((I assume not but just asking what is this code actually doing?) Appreciate any help.

 

[Excelerate2014]

I am receiving a Run-Time error '1004':

The name that you entered is not valid

 

[mikerickson]

Apparently Excel uses hidden names for some behind the scenes uses. Try running this sub. Just copy paste it into a normal module and run it.

Sub test()
Dim oneName As Name

MsgBox "There are now " & ThisWorkbook.Names.Count & " names in the workbook."

On Error Resume Next
    For Each oneName In ThisWorkbook.Names
        If Not oneName.Visible Then
            oneName.Delete
        End If
    Next oneName
On Error GoTo 0

MsgBox "There are now " & ThisWorkbook.Names.Count & " names in the workbook."
End Sub

 

[Excelerate2014]

yikes... That one definitely ran. Message box popped up that says "There are now 6,532 names in the workbook" does that sound right? Thanks

 

[Excelerate2014]

Hit OK and its processing those 6,532 names I assume?

 

[Excelerate2014]

Now only 90 names. Most likely just my names. Awesome! Thanks

 

[Excelerate2014]

I count 86 names in the Name Manager (all mine). I guess there are 4 that will always remain hidden. No more pop-ups on copy. Thanks again.

 

[Fazza]

Maybe too late, but a variation. Deleting the names that start with "BLPH"

I've referred to the activeworkbook.names

Thinking you could put the code in your personal macros & run it easily on many workbooks. Or with more complexity make the code auto-run every time a file opens.