vba to delete specific name from Name Manager

[HGood]

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've 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've tried in Name Manager:
- I've unprotected the specific worksheet that the link refers to.
- I've moved the linked document to another folder to destroy the link
- I've 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]

Try this line of code (you can execute it right from the Immediate window if
you like)...

Names("AcctName").Delete

 

[HGood]

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 doesn't even find the
name, which is guess it wouldn't since Name Manager wouldn't allow 2
identical names.

I had saved prior to executing this, but upon reopening I'm back to two
"AcctName" Names, one which is good, the second with a value of !REF#.

So I don't know what to do now, but thank you for your help anyway.
Harold

 

[Rick Rothstein]

You didn't mention that you had two Names with the same name in your
original post. I've never seen that before and I don't 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.

 

[Dave Peterson]

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

 

[HGood]

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 couldn't 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]

I would have guessed that any name that refered to something that included the
#Ref! error was bad and could be deleted.

The instr() function did exactly what you asked for--it just looks for those
characters in that .refersto string. You didn't 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'm not sure what Name Manager you're 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]

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 didn't find it.

I was refering to the Name Manager in 2007.

Again, thanks for your help. I'll have a look at the other Name Manager you
mentioned.
Harold

 

[Dave Peterson]

Ahhh. That makes sense.

You could change the code to look for just #ref. Maybe add a prompt to ask if
it's ok to delete that name???

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 didn't find it.

I was refering to the Name Manager in 2007.

Again, thanks for your help. I'll have a look at the other Name Manager you
mentioned.
Harold