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 wrote:

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" wrote:

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 wrote:

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" wrote:

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

Names("AcctName").Delete

--
Rick (MVP - Excel)


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





--

Dave Peterson


--

Dave Peterson