Deleting names with errors
 

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

--
Return email address is not as DEEP as it appears

Deleting names with errors
 

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

Deleting names with errors
 

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" wrote in message
...
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

Deleting names with errors
 

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

Deleting names with errors
 

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" wrote in message
...
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

Deleting names with errors
 

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