Deleting names with errors

[Jack Sheet]

Hi all

Is there a quick and easy VBA method of deleting all names that contain
errors?
I don't mean EVALUATE to errors, that would be too difficult.
I mean if I have a name that contains
=#REF!$A$1:$A$100
or
=#N/A
etc

Thanks

 

[Jan Karel Pieterse]

Hi Jack,

Is there a quick and easy VBA method of deleting all names that contain
errors?
I don't mean EVALUATE to errors, that would be too difficult.
I mean if I have a name that contains
=#REF!$A$1:$A$100

Consider downloading the
Name manager (by Charles Williams, Matthew Henson and
myself) from:

www.jkp-ads.com/Download.htm

or

www.oaltd.ie/mvp

or from:

www.decisionmodels.com/downloads.htm

You can filter the list of names for those containing errors and then do
anything you want with them.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[Jack Sheet]

Hi Jan

Yes, I already use that a lot, and it is really GREAT!

But it does not help my particular case:

Embedded within a workbook I have macros the execution of which result in
the generation of unwanted names (usually the macro involves some worksheet
duplication or deletion, and those worksheets refer to names local to other
worksheets, resulting in some "white noise" appearing in the list of names).

I want to include some additional code within the existing macros, so as to
"clean up" (ie discard) the surplus names. I am fairly confident that if I
delete all names containing errors that would do most of the work required.

I would not know how to call features of Name Manager from within a VBA
routine, and anyway I would want to distribute the workbook to others who
might not have Name Manager.

 

[Jan Karel Pieterse]

Hi Jack,

I want to include some additional code within the existing macros, so as to
"clean up" (ie discard) the surplus names. I am fairly confident that if I
delete all names containing errors that would do most of the work required.

You could use something like this (untested):

Sub RemoveNamesWithErrors(oWkbk as Workbook)
Dim oNm as Name
For Each Onm in oWkbk.Names
If Instr("#Ref!",Onm.Refersto)>0 Then
Onm.Delete
End If
Next
End Sub

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[Jack Sheet]

Thanks
This seems to be close. Just one problem:
One of the surplus names that is created refers to #N/A
I tried adding the line
If Instr("#N/A",Onm.Refersto)>0 Then Onm.Delete
immediately after the test for #REF!
but it does not delete that name.
Any suggestions? Thanks

 

[Jan Karel Pieterse]

Hi Jack,

I tried adding the line
If Instr("#N/A",Onm.Refersto)>0 Then Onm.Delete
immediately after the test for #REF!
but it does not delete that name.

Change this line in my original code :

If Instr("#Ref!",Onm.Refersto)>0 Then

To:

If Instr("#Ref!",Onm.Refersto)>0 Or Instr("#N/A!",Onm.Refersto)>0 Then

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com