Reseting named ranges.

[Guest]

In Lotus 123 I could rest all named ranges at one time using /rnr. Is there
a way to do this in Excel?

 

[Guest]

There isn't a reset all function, but the following code will remove all
range names from the current workbook:

Sub RemoveNames()

Dim wb As Workbook
Dim n As Name

Set wb = ActiveWorkbook
For Each n In wb.Names
n.Delete
Next n

Set wb = Nothing
Set n = Nothing

End Sub

 

[Gord Dibben]

Download JK Pieterse's Name Manager Add-in.

It is capable of many functions.

http://www.oaltd.co.uk/mvp/


Gord Dibben MS Excel MVP

 

[Guest]

Thanks for the help. I'll give it a try.

There isn't a reset all function, but the following code will remove all
range names from the current workbook:

Sub RemoveNames()

Dim wb As Workbook
Dim n As Name

Set wb = ActiveWorkbook
For Each n In wb.Names
n.Delete
Next n

Set wb = Nothing
Set n = Nothing

End Sub

 

[Paul D. Simon]

Jim,

You can indeed still use /rnr in Excel to reset all range names just
like in Lotus 1-2-3 without the need for a special macro or function.

1.) Do the menu sequence Tools>Options, then in the resultant dialog
box, click the Transition tab.

2.) In the "Microsoft Excel Menu of Help Key" field, enter a /, then
choose the "Lotus 1-2-3 Help" radio button and click OK.

3.) Now from your worksheet, hit just the / key on the keyboard to
bring up "Help for Lotus 1-2-3 Users" dialog box.

4.) Click the "Demo" radio button, then click the "Faster" button until
the number reaches 5 and click OK.

5.) From now on, simply do /rnr to reset all your range names. (Note
that while Excel performs this for you, it will be showing you a "demo"
by going through all the screens for you. Don't worry about that -
just let it happen; Excel will indeed be performing /rnr for you.)

By the way, now that you'd set this up, you will find that most (but
not all) Lotus 1-2-3 "/" commands will now work in Excel, and as you
type them, you actually see the old Lotus 1-2-3 menu sequences appear
on screen.

Let me know if this was a help to you.

Paul