Delete named ranges from individual worksheet

Domski

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
Hi,

I was after a bit of code to delete the range names on a particular worksheet I thought it couldn't be that hard but have only been able to find this in the archives, unfortunately I get an error when trying to execute it:

Code: 
Sub Delete_My_Named_Ranges()
Dim n As Name
Dim Sht As String
' Put in name of sheet where the range is located
Sht = "Org Lookups"
For Each n In ThisWorkbook.Names
If n.RefersToRange.Worksheet.Name = Sht Then
n.Delete
End If
Next n

The error I'm getting is Run-Time error 1004, Application defined or object defined error.

Any suggestions how to fix this?

Dom
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
Dom

Sub works fine for me.:confused:

Do you have any protection or anything?
 
Upvote 0
Do you have any names that don't refer to ranges - named formulas or constants for example?
 
Upvote 0
Cheers Jonmo but alas no, I still get the same problem. You'd think it would be quite straightforward but it appears not.

I can delete all the named ranged in the workbook quite easily but I then lose a bunch that I want to keep and have to go about defining them again which seems a waste of time.

Dom
 
Upvote 0
No protection or names other than relating to ranges on another worksheet in the workbook.
 
Upvote 0
Dom

Care to post some examples of your named ranges?

I just tested with simple ones, no OFFSET or references to other worksheets.:)
 
Upvote 0
Which line is causing the error - the Delete or the one before it (or another)?
 
Upvote 0
The line that's erroring out is:

Code: 
If n.RefersToRange.Worksheet.Name = Sht Then

An example of the range names would be:

Name: Adult_Social_Care_50011628
Refers to: ='Org Lookups'!$D$2:$D$5

There are about 150 named ranges like this that drive cascading data validation but nothing complex.

Dom
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
Unable to delete name range
Replies
3
Views
690
HaHoBe
HaHoBe
rjplante
  • Solved
Change from Set as Default to Set as Active printer
Replies
1
Views
217
NdNoviceHlp
NdNoviceHlp
S
  • Solved
How to delete a single named range from a worksheet
2
Replies
10
Views
495
sspatriots
S
R
  • Solved
SaveAs method not actually saving, wrong sheets being deleted (1004 Error)
Replies
4
Views
404
Alex Blakenburg
A
P
  • Solved
Existing macro to password protect sheets has stopped working
Replies
2
Views
938
purplekombi
P

Forum statistics

Threads
1,214,919
Messages
6,122,259
Members
449,075
Latest member
staticfluids

Share this page

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