Delete named ranges from individual worksheet

[Domski]

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:

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

 

[Domski]

Ok, weird. I just set up a few ranges in a blank workbook and it works fine. Still won't work in the one I want it to though.

 

[RoryA]

How about:

Sub Delete_My_Named_Ranges()
   Dim n As Name
   Dim Sht As String
   On Error GoTo err_check
   ' 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
   Next n
   
Exit Sub
err_check:
   MsgBox "Name: " & n.Name & vbCrLf & "RefersTo: " & n.RefersTo & vbCrLf & "caused an error"
   Resume Next
End Sub

 

[Domski]

Thanks, that works fine other than the error handler produces the following error:

Name: 'Org Lookups'!_FilterDatabase Refers To: ='Ord Lookups'!#Ref! caused an error.

Part of the code that creates the lists that the named ranges are defined in uses advanced filter to create unique lists and then combines/deletes columns which I'm guessing is creating and then screwing that reference up as it's not one of mine.

Must start using error handlers more in my code I guess.

Dom