Workbook vs Worksheet names collection problem
Workbook vs Worksheet names collection problem
(OP)
I am trying to delete named ranges in my VBA routine. I use sheet specific name, however, Excel also adds the name to the non-sheet specific. For example, ActiveWorkbook.Names("test").RefersTo is different than Activesheet.Names("test").RefersTo
I want to delete the workbook level name w/o deleting the worksheet level one. I can do it when I use the regular excel menu .. Insert, Names, Define and then select the name and hit delete (while on a sheet that does not have a sheet specific name) but I cannot do it in VBA. It always deletes the sheet level name first.
Any help would be appreciated, Thanks
I want to delete the workbook level name w/o deleting the worksheet level one. I can do it when I use the regular excel menu .. Insert, Names, Define and then select the name and hit delete (while on a sheet that does not have a sheet specific name) but I cannot do it in VBA. It always deletes the sheet level name first.
Any help would be appreciated, Thanks





RE: Workbook vs Worksheet names collection problem
Dim n As Name
For Each n In Names
' Debug.Print n.Name, n.RefersTo
If n.RefersTo = "=Sheet1!$I$10" Then
Debug.Print n.Index
' n.Delete
End If
Next n
Good luck!
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.