deleting all Names at once

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
found some topics deleting names in VBA

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

QUESTION:
Is there a possibity to delete all Names all at once ?
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Manually

You can use Lotus Transition to deletes all names in the workbook (something which Excel can't manage in one operation).

1 Tools menu | Options |Transition tab.
2 Click the radio button for Lotus 1-2-3 Help.
3 Click the check box for Transition Navigation keys.
4 OK.
5 /RNR (in any cell, type in a forward slash, followed by RNR).
6 When the Define Name Dialog box appears, click OK
7 Repeat step 1, and reverse steps 2 and 3, OK.

Macro
(deletes all named ranges except for the Print Area)
Code:
Sub deleteRanges()
'Ivan Moala
'http://www.mrexcel.com/board2/viewtopic.php?t=64516

Dim WBname As Name
'Delete all ranges
For Each WBname In ActiveWorkbook.Names
    If Not WBname.Name Like "*!Print_Area" And _
        Not WBname.Name Like "*!Print_Titles" Then
        WBname.Delete
    End If
Next

End Sub
Regards,

Mike
 
Upvote 0
Thanks, Mike, for your help, so quicly !
I think "deleting all names at once" is a missing tool in Excel: could be so usefull to clean up files.

In fact I've written VBA to extract some important information from a larger Excelfile (="source") to an "extract". Since there is a sheet in the "source" with three charts on it, the easiest way to insert this sheet into the "extract", was to simply copy the whole sheet, but this is taking about 1360 names of the "source" to the "extract" adding some hundreds of unneeded kb.
I can't import your solution in my macro, or can I ? (And it should be working for hundreds of other users, since my programm is succesfull in the "field".)

Or is there a way to prevent all those unnessecary names to "come over" ?

Would be delighted to make a 50 kb instead of 500 kb "extract" in 5 seconds. (deleting all names takes 148 seconds on my machine - 2 years old !)

regards,
Erik
 
Upvote 0
Thanks, Mike, for your help, so quicly !
I think "deleting all names at once" is a missing tool in Excel: could be so usefull to clean up files.

In fact I've written VBA to extract some important information from a larger Excelfile (="source") to an "extract". Since there is a sheet in the "source" with three charts on it, the easiest way to insert this sheet into the "extract", was to simply copy the whole sheet, but this is taking about 1360 names of the "source" to the "extract" adding some hundreds of unneeded kb.
I can't import your solution in my macro, or can I ? (And it should be working for hundreds of other users, since my programm is succesfull in the "field".)

Or is there a way to prevent all those unnessecary names to "come over" ?

Would be delighted to make a 50 kb instead of 500 kb "extract" in 5 seconds. (deleting all names takes 148 seconds on my machine - 2 years old !)

regards,
Erik


Hi

If you are using Excel 2010, open the Names dialog window (Ctrl + F3), then using the bottom LHS of the window pull open to increase the size of the window to max, then highlight all by using your mouse (press the RH button down and highlight all rows), then press the delete button and then OK.

Gary
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top