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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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
 
Upvote 0
Try

Code:
Sub test()
Dim nm As Name
For Each nm In ThisWorkbook
    nm.Delete
Next nm
End Sub
 
Upvote 0
and here's another

Code:
Sub wipeRangeNames()
    Dim rn As Variant
    For Each rn In ThisWorkbook.Names
        rn.Delete
    Next rn
End Sub
 
Upvote 0
Unfortunately I get a run time error '1004'

when I debug, the following is highlighted as an error: rn.Delete
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

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