Clear named ranges

m24jones

Board Regular
Joined
May 19, 2002
Messages
61
There are over 100 named ranges in my excel 2007 workbook. Is there an easy way to clear all of these out?

Thanks.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
One way

Code:
Public Sub Clear_NR()
Dim i As Long
For i = ThisWorkbook.Names.Count To 1 Step -1
    ThisWorkbook.Names(i).Delete
Next i
End Sub
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
Sub test()
Dim nm As Name
For Each nm In ThisWorkbook
    nm.Delete
Next nm
End Sub
 

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
and here's another

Code:
Sub wipeRangeNames()
    Dim rn As Variant
    For Each rn In ThisWorkbook.Names
        rn.Delete
    Next rn
End Sub
 

m24jones

Board Regular
Joined
May 19, 2002
Messages
61

ADVERTISEMENT

Unfortunately I get a run time error '1004'

when I debug, the following is highlighted as an error: rn.Delete
 

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,939
Maybe try this:

Code:
sub test()
For Each myName In Names
    If Not myName = "=#NAME?" Then myName.Delete
Next myName
end sub
Hope that helps.
 

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196

ADVERTISEMENT

Unfortunately I get a run time error '1004'

when I debug, the following is highlighted as an error: rn.Delete

Hmm, worked fine on xl2003
 

m24jones

Board Regular
Joined
May 19, 2002
Messages
61
Oh well - I have Excel 2007; perhaps that is the issue.

I tried the new Macro and get the same runtime error. When I debug, the "myName.Delete" is highlighted.

Here is the full text of my macro:

Sub test()
For Each myName In Names
If Not myName = "=#NAME?" Then myName.Delete
Next myName
End Sub
 

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
Does it actually delete anything at all or does the loop never execute?

I'm wondering if there are some reserved names you can't delete.
 

m24jones

Board Regular
Joined
May 19, 2002
Messages
61
I changed calculation mode to automatic from manual and the script started running. It's been going for 10 minutes - is that to be expected (there are hundreds of names)? Thanks so much for your help!
 

Forum statistics

Threads
1,136,341
Messages
5,675,207
Members
419,553
Latest member
hanahass

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
Top