Easy way to delete names in a workbook?

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?
 
B

Bob Phillips

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)
 
K

Ken Wright

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!!
 
N

Norman Harker

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.
 
R

RagDyer

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!!
 
D

Dave Peterson

/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
 
R

RagDyer

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
 
D

Dave Peterson

Oops.

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

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

RagDyer

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!
 
N

Norman Harker

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.
 
B

Bob Phillips

Agreed. It is no different than deleting them individually through Excel or
with VBA,

Bob
 
N

Norman Harker

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.
 
K

Ken Wright

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 :)
 
B

Bob Phillips

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.
 
N

Norman Harker

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.
 
B

Bob Phillips

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?).
 
K

Ken Wright

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!!!!>
 
K

Ken Wright

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!!! :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

automaitcally update external defined names? 3
Copy worksheet containing names to other workbook w/o problems? 1
Delete defined names 1
Defined Names in Formulas when copying Worksheets in a Workbook 1
Linked Files - How to find reference 3
Excel names in workbook - how to delete all? 4
Macro changing region names 2
version differences in excel 2007 - use of macros & range names 1

Top