Workbook vs Worksheet names collection problem

[NickJ]

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&quot.RefersTo is different than Activesheet.Names("test&quot.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

 

[joerd]

I had never realized that Excel is so sloppy in managing this. You are right, if you have the same name defined on multiple sheets, you can only get to one of them. The workaround might be to loop through the names collection and selecting the name by the range it refers to:

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.