How do I find excel names that do not show in the define function

S

Smitty

,I was given the file. It has many names some using Asian characters. I
need none of them, but I get a long annoying sequence of queries regarding
them whenever I try to copy a sheet. (this I do often) How can I find and
eliminate these names? They do not show up when I click on the insert names
menu.
 
D

David McRitchie

Hi Smitty,
You can use a macro by "Mike" kind of like you just a single
unidentifiable username in the newsgroup.

http://groups.google.com/groups?threadm=CDF9047F-95BD-4C28-AE9E-83EF93DE310A@microsoft.com

the macro will go through the list of name and delete each one.

Sub DeleteallNames()
Dim nName As Name
For Each nName In Names
nName.Delete
Next nName
End Sub

Google Groups has sure gone downhill, the purpose of posting
code is so that it can be copied, Google site is taking forever
when it is functioning and the new layout for GG causes major
problems and now the messing up mouse and context menus.
I would have rather have just pointed you to the code, but I
have bookmarklets to change things (and could probably
work from original text).
 
B

Bob Umlas, Excel MVP

Or use this:
Sub ShowAlllNames()
Dim nName As Name
For Each nName In Names
nName.Visible=true
Next nName
End Sub
 
D

Dave Peterson

If you're working with names, get Jan Karel Pieterse's (with Charles Williams
and Matthew Henson) Name Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

It'll make working with names much easier.
 
S

Smitty

Dave,

THX! This is my first time in the discussion group and I am surprised at
the timeliness and accuracy of all the help I received. The macro you
offered did exactly what I wanted.

Thanks again,,, Bernie Smith
 
S

Smitty

Dave,

I had so much luck with the macro that you gave me that I have tried
runninhg it on other files that I have been using. (I get some of these from
Korean employees) On one file get a run time error 1004 "That Name is not
valid".

Any suggestions on how to delete these?

Thanks,,, Bernie
 
D

David McRitchie

Hi Bernie,

Before you get too ambitious deleting any/all names,
you should be aware that they are usually in use
within worksheet formulas or in VBA coding; otherwise,
I don't think they would have been created in the first place.

Deleting a name that does not exist
ActiveWorkbook.Names("bcd").Delete
would get the error you describe, but I have no idea
how you find the name with the macro and can't delete it.

I don't know of any way to tell if a defined name is
in use within the workbook, and it would be harder
to determine whether macros are using a defined
name. Besides with concatenation to create a
defined name on the fly you wouldn't be able to tell
if a specific defined name is used or would be needed.

For instance defined name ranges are very useful
in VLOOKUP Worksheet Function
http://www.mvps.org/dmcritchie/excel/vlookup.htm

If you delete a name in use you will get results
=SUM(abc) showing 12
after deleting all the names you would get
=SUM(abc) showing #NAME?

Trying to color a range that is no longer there in VBA
and the range might be used only in the VBA code and
not actually in use in worksheet formulas (or conditional formatting).
Range("abc").Interior.ColorIndex = 6
would result in
Run-time error '1004':
Method 'Range' of object '_Global' failed
 
S

Smitty

Dave,

The names at issue are visible in the Insert/Names pulldown utility, but
when I select one and click delete, it does not go away and I get no error
message.

When I go after it in the macro I get the "Name invalid" message mentioned
before.

I absolutely do not need them and I am willing to try anything to get rid of
them.

With the "ActiveWorkbook.Names("bcd").Delete" line of code you offered, I
got an "Application not defined" message.

Any other ideas? (I really appreciate your help.)

Thanks,,, Bernie
 
D

Dave Peterson

You could try this in excel (not the VBE):

tools|Options|General tab|Check R1C1 reference style

Excel will look at the names and prompt you to supply a valid name for each
invalid name.

The Name Manager has this feature built in.
 
Top