Removing Names

[ranswrt]

I made the mistake of writing a procedure that deleted selected sheets in my
workbook. I deleted the sheets without deleting the name cells in that
sheet. Now when I use 'F3' to paste the list of named cells I have about 800
names that don't exist in my workbook. How can I remove these?
Thanks

 

[JLGWhiz]

I made the mistake of writing a procedure that deleted selected sheets in my
workbook. I deleted the sheets without deleting the name cells in that
sheet. Now when I use 'F3' to paste the list of named cells I have about 800
names that don't exist in my workbook. How can I remove these?
Thanks

 

[Bob Phillips]

Sub DeleteNames()
Dim nme As Name

For Each nme In ActiveWorkbook.Names

If Left(nme.RefersTo, 6) = "=#REF!" Then

nme.Delete
End If
Next nme
End Sub

Take a backup before you do this and make sure the others are okay.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[ranswrt]

Thanks that worked great. I had about 800 names to delete.

I have another question. I am building a workbook with procedures to add,
delete, and modify different databases. The list can grow to be very large.
Is it better, more efficient, and faster to have the list on one sheet or
have each database on its own worksheet that might be deleted when the
database is no longer needed?
Thanks

 

[Bob Phillips]

I don't know about more efficient, but if they are big lists, it might just
be more manageable visually to have them on separate worksheets.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)