Excel - vba to delete specific name from Name Manager

Asked By HGood on 14-Sep-09 12:56 PM

Hi, somehow I ended up with several names in Name Manager, value is !Ref#,
with a link to another spreadsheet. I have tried everything I can think of to
delete it within Name Manager. But when I select it, the Edit and Delete
options are grayed out. I have wasted enough time on that.
Can anyone help me with VBA code to delete a the name "AcctName"?
I'd like to see if I can to it this way, before wasting more time within
Name Manager.
Thanks,
Harold
PS Some will wonder what I have tried in Name Manager:
- I have unprotected the specific worksheet that the link refers to.
- I have moved the linked document to another folder to destroy the link
- I have broken the link in Edit Link
None of the above will ungray the Edit or Delete buttons in Name Manager
when I select this bad Name.

Rick Rothstein replied on 14-Sep-09 01:06 PM

Try this line of code (you can execute it right from the Immediate window if
you like)...
Names("AcctName").Delete
--
Rick (MVP - Excel)

HGood replied on 14-Sep-09 01:29 PM

Thanks Rick,
Unfortunately it deleted the good name by the same name. When I execute it
again I get a 1004 Run Time error message, like it does not even find the
name, which is guess it would not since Name Manager would not allow 2
identical names.
I had saved prior to executing this, but upon reopening I am back to two
So I do not know what to do now, but thank you for your help anyway.
Harold

Rick Rothstein replied on 14-Sep-09 02:02 PM

You did not mention that you had two Names with the same name in your
original post. I have never seen that before and I do not know how to duplicate
your situation for testing. Can you post your workbook on line somewhere so
some of us can download it in order to look at it directly? If you are
unwilling to make your workbook so widely available, you can (if you are
willing) send me a copy of it (remove the NO.SPAM stuff from my email
address) and I will be happy to look at it. I would note, though, that there
are other more qualified volunteers in these newsgroups than I, so the
public posting would be the better way to go if at all possible.
--
Rick (MVP - Excel)

Dave Peterson replied on 14-Sep-09 04:13 PM

Maybe you can just look for that #ref! error and delete them:
Option Explicit
Sub testme()
Dim myName As Name
For Each myName In ActiveWorkbook.Names
If InStr(1, myName.RefersTo, "#ref!", vbTextCompare) > 0 Then
myName.Delete
End If
Next myName
End Sub
--
Dave Peterson

HGood replied on 14-Sep-09 07:50 PM

Dear Dave,
Thanks for your help. I had to modify the code below because the "Refers To"
in my Name Manager was actually a path and range, of which !REF# was a part.
So for your "!REF#" below, I substituted the entire path and range, had to
type it all in since i could not select it. But I did that for all three bad
ones and it deleted them each. Thanks so much!
Just a question, instead of typing all those lengthy paths, would there have
been a way to use a wildcard *, so if !REF# was a part of the "Refers To", it
would have deleted it?
Many thanks,
Harold

Dave Peterson replied on 14-Sep-09 10:16 PM

I would have guessed that any name that refered to something that included the
The instr() function did exactly what you asked for--it just looks for those
characters in that .refersto string. You did not need to include the path at
all.
On the other hand, it may have been safer.
If you had a sheet named "HGood #Ref! Error Sheet", then any name that included
that sheet name would have been deleted.
ps. I am not sure what Name Manager you are refering to--xl2007's built in
version or Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name
Manager.
But you may want to try the third party version:
NameManager.Zip from http://www.oaltd.co.uk/mvp

HGood replied on 15-Sep-09 03:45 PM

Dave, thanks for the clarification. I think the reason it did not find them
is that because it was a path to another spreadsheet in another folder, the
path showing in Name Manager was something like:
'[Mbeya TSC Budget Template 2bSep09.xls]#REF'!$k$211:$k$215
Notice that after the F is the ', not the !. That's the way it was in Name
Manager. Therefore it did not find it.
I was refering to the Name Manager in 2007.
Again, thanks for your help. I will have a look at the other Name Manager you
mentioned.
Harold

Dave Peterson replied on 15-Sep-09 03:57 PM

Ahhh. That makes sense.
You could change the code to look for just #ref. Maybe add a prompt to ask if
it is ok to delete that name???