Easy way to delete names in a workbook?

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

I inherited a workbook. The guy defined about 50 names,
referring to a bunch of other workbooks. Is there an easy
macro to delete all defined names rather than doing it one
by one?
 
Joe,

Try this

Sub DeleteNames()
Dim nme As Name

For Each nme In ActiveWorkbook.Names
nme.Delete
Next nme

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Previously posted by Norman Harker:-

And here's a best kept secret of Excel but it comes with a health warning.

Tools > Options > Transition
Check Transition Navigation Keys

Now:
/RNR

Removes every single name in the workbook!

Beats doing them one at a time although you can use VBA or various Add-Ins.

But it really ought to come with a health warning after the /RNR command as
it can't be undone! And with many hundreds of names in a workbook, R is next
to T and if you really wanted a Table of your names... Don't work on Excel
when your Mother is in the room!!
 
Hi Ken!

Mum! Leave the room!

This doesn't work in Excel 2003.

You can come back now!

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Alright Ken,

What aren't you telling me !?

What exactly does
<<"Now:
/RNR">>
Mean ?

I can't get it to work for me.

XL97 SR-1
WB saved as a 5.0/95

Tried individual keystrokes, and then holding down the forward slash(/).

Tell me what I'm not doing right.
--


Regards,

RD

YES, I did switch transition keys.
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------


Previously posted by Norman Harker:-

And here's a best kept secret of Excel but it comes with a health warning.

Tools > Options > Transition
Check Transition Navigation Keys

Now:
/RNR

Removes every single name in the workbook!

Beats doing them one at a time although you can use VBA or various Add-Ins.

But it really ought to come with a health warning after the /RNR command as
it can't be undone! And with many hundreds of names in a workbook, R is next
to T and if you really wanted a Table of your names... Don't work on Excel
when your Mother is in the room!!
 
/RNR
/ to bring up 123's menu
R = Range
N = Name
R = Reset

Are you sure you did:
Tools > Options > Transition
Check Transition Navigation Keys
 
Yep, I sure did.

Even Saved, Closed, and re-opened.

The forward slash doesn't do a thing to my WB.

I don't know, feel like I'm not loved !

I used one of my real old WBs,
first, because Ken said there was no going back,
and second, because I figured the older the XL, the closer to the 1-2-3 it
(XL) was copied from.
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

/RNR
/ to bring up 123's menu
R = Range
N = Name
R = Reset

Are you sure you did:
Tools > Options > Transition
Check Transition Navigation Keys
 
Oops.

Make sure you:
tools|options|transitions|lotus 123 help (not MS menus)

The disappointing thing is that I used it yesterday and forgot!
 
Well David, THANKS !

I guess Ken could have been a little more precise with his help from the
start.

But, as far as not going back, "can't be undone", I don't understand ...
just close without saving.

The way it was described, it sounded as if the WB would turn into a pumpkin
as soon as one hit the "magic keys".

Anyhow, thanks again, something nice to add to the repertoire !
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

Oops.

Make sure you:
tools|options|transitions|lotus 123 help (not MS menus)

The disappointing thing is that I used it yesterday and forgot!
 
Hi RagDyer!

If you're in Excel 2003 this doesn't work but for Excel 2002 and below
it does.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Agreed. It is no different than deleting them individually through Excel or
with VBA,

Bob
 
Hi Bob!

The only difference is that like a VBA process it deletes all names in
one hit rather than have to go through individually.

Can't be undone? Well it can't be reversed using Edit > Undo and
that's the usual interpretation of "undone". That leaves the old
standby of closing without saving or retrieving your backup.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
I guess Ken could have been a little more precise with his help from the
start.
Click to expand...

Sheeeeeeeeeeeeshhhhhhhhhhh - Give me a break, please!!!! :-)

As I stated right up front, it was a previous post of Norman's, which I did not
feel any need to edit as there are only a couple of transition options you can
set. I also didn't see any of the follow ups because I work in a different
timezone to most of you folks, and it is only early on a Saturday morning here
in the UK, so first time I have seen any responses to the note at all. AND
WHAT'S MORE - I haven't had my coffee or breakfast yet!!!, so I'm doing my
Groucho impersonation until the damn kettle has boiled <Grrrrrrrr - where's that
caffeine>.

Apart from that I'm glad you found it useful, thank you Norman, and good morning
everyone :-)
 
Come on Ken, you know that being a regular poster here you have to be
precise, comprehensive, unambiguous, and totally accurate, or there are
always those ready to jump on it .

Get that coffee (Nescafe Alta-Rica is my recommendation if it is instant),
eat your toast, and stop whingeing<vbg>.

Bob

Oh, and good morning to you.
 
Hi Bob!

Re: "stop whingeing"

No chance of that. He's a Pom!!

<gdr>

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Okay Ken, you from the left, I'll take the right.

By the way, isn't a certain Mr Harker an Essex boy (do they wear white
high-heeled sandals?).
 
Essex boy

They're all Northeners to me!!!!!!!! :-)

Coffee had kicked in, breakfast had settled, and Ken was chilling out, and then
work just called, on a Saturday, to ask me if I could possibly postpone a day of
my vacation next week, so that I can go in and rerun a set of numbers for a bid
that is due out next week - Aaaaaagghhhhhhh - leave me alone!!!!!!!!
<Grrrrrrrrrr - more coffee needed now, and where's those Chill Pills!!!!>
 
If somebody hadn't already invented it, I'd have to do so by tomorrow at the
latest, because I couldn't go much longer than that without it!!! :-)
 
Back
Top