In Excel2007, we have several large spreadsheets that use worksheet scoped range names. However for various reasons we can not control - some users also have some old global scoped range names in the same book which happen to have the same names. We are trying to clean out the books of all these old global names. As there are dozens of copies of these files each containing hundreds of these old global names, going into the name manager to physically select the global (workbook) level name for each range to delete is not going to be practical.
The specific problem occurs when that same name exists as both a workbook level name and a worksheet scoped level name. We want to delete the workbook level name but retain the local worksheet level name which is correct.
I have confirmed that i can use the ValidWorkbookParameter property of a range name to determine if there is a global (workbook) level instance of that name but when it then does go to delete the name, it actually deletes the local scoped instance too.
Does anyone have advice for how to limit the delete to JUST the global (workbook) scoped instance of the name? Any help would be appreciated.
Dim nName As Name
For Each nName In ThisWorkbook.Names
If nName.ValidWorkbookParameter = False Then
MsgBox nName.Name 'just used for confirmation test purposes