Delete ALL Named Ranges on ACTIVE Worksheet?


Posted by JAF on September 20, 2001 6:01 AM

Hiya

I have a piece of code that deletes all named ranges from the active workbook:
Sub Delete_ALL_Named_Ranges()
Dim n As Name
For Each n In ActiveWorkbook.Names
n.Delete
Next n
End Sub

What I need for a spreadsheet I'm currently working on is a way to delete all the named ranges, but only from the active worksheet.

For example, in a 3 worksheet workbook, Sheet1 and Sheet 3 contain named ranges that do not change. Sheet2 contains several named ranges all of which need to be deleted and re-created when the macro is run. In other words, I want to delete all the named ranges from Sheet2, but to leave any named ranges on Sheet1 and Sheet3 intact.

I suppose that I could settle for deleting all named ranges from the entire workbookk and then re-creating those I still need, but there has to be a more "elegant" solution.

Any suggestions??


JAF



Posted by Mark O'Brien on September 20, 2001 6:54 AM

Hi,

I don't know if you wanted to delete the data as well or just the range names, but this code will delete the range name on Sheet 2.


Sub Delete_My_Named_Ranges()
Dim n As Name
Dim Sht As String

' Put in name of sheet where the range is located
Sht = "Sheet2"

For Each n In ActiveWorkbook.Names
If n.RefersToRange.Worksheet.Name = Sht Then
n.Delete
End If
Next n

End Sub