Deleting non-existent 'Names'

[mach3]

i have a simple macro that creates Names. however, when a worksheet(s) that contains references to a Name is deleted, the list of Names in the Define Names box still contains a listing of that name. However, the reference to that Name is #ref'd (e.g., '#REF!$A$1).

since users of this excel file will be creating/deleting many worksheets, there will be a long list of un-used, un-referenced, 'dead' Names.

#1 - is there a maximum # of Names allowed? i don't want users to exceed this limit and crash the tool.

#2 - even if #1 is not a concern, managing a long list of Names (of which a vast majority will be junk) is not a good thing. can someone offer up a short macro that checks for bogus Names and deletes them from the Names list?

thanks for your attention.


mach3

 

[Anne Troy]

1. Limited only by the available memory on the PC.

 

[Dave Hawley]

Hi Mac

Run this code on open or close etc

Sub DeleteREFnames()
Dim nmRef As Name
 
 For Each nmRef In ActiveWorkbook.Names
   If Left(nmRef.RefersTo, 5) = "=#REF" Then nmRef.Delete
 Next
 
End Sub

 

[mach3]

thanks. i just added the macro to an existing macro and works fine.

mach3