Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
deleting names from the name box | Excel Discussion (Misc queries) | |||
deleting duplicate names | Excel Discussion (Misc queries) | |||
deleting duplicate names | Excel Worksheet Functions | |||
Deleting Names | Excel Programming | |||
Deleting range names | Excel Programming |