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,

 

[Excelerate2014]

Thanks Fazza, that would be great. Can you share the code?

 

[Fazza]

Got interrupted & forget the code. Whoops.

Dim nm As Excel.Name


For Each nm In ActiveWorkbook.Names
  If nm.Name Like "BLPH*" Then nm.Delete
Next nm
Set nm = Nothing

 

[Fazza]

Another approach would be some code to loop through all Excel files in a directory and for each one open the file, delete the unwanted names, save & close the file.