Deleting named ranges in the name manager fails to make the name available for re-use

I have this same issue. Has anybody has success with an issue like this?

Hi Guy's and welcome to the forum.

Give this code a run and see if it helps out. It will delete all named ranges, so you may need to tweek it or test it in a copy of your workbook, don't test on real workbook.

Sub DeleteNames()
Dim nm As Name

On Error Resume Next
For Each nm In ActiveWorkbook.Names
nm.Delete
Next
On Error GoTo 0


End Sub

Click to expand...

Thank you Trevor. This worked, but is there a way to manually delete the names from the "Activeworkbook.names" that the code references? I have two problems that I need to work around:

- We have too many print ranges and other defined ranges to make using this solution viable.

- We have a mix of excel 2010 and 2003 users. If 2010 users use this macro, they will have to do some additional steps to get the macro to go away (http://www.mrexcel.com/forum/showthread.php?t=518757&highlight=modules+deleted). The 2003 users have tremendous fear of the Macro warning when they open books, so the files must be macro free. Books are also saved as xls at this point to save the '03 users the time to wait for excel to convert the xlsx.

Brett

Manual way would be via the Formula tab and also look at print areas to see if additional names have been used.

That is what prompted this problem, there are no names listed in name manager. For example, I have a blank workbook that opens up when excel starts. It has one tab, no modification from a blank workbook except column width changes and a footer that says my name and the file path. If I need more tabs, I'll right click the one tab and copy it as a new tab.

Excel will then inform me that "A formula or sheet you want to move contains the name'_Fill', which already exists on the destination worksheet ......" and then proceed to rename the name in my newly copied sheet.

I can delete the footers, select the whole sheet and delete the cells, remove the print areas, check the name box & name manager (which will show nothing), but when I copy, I still get the error. Your script works perfectly.

I can even make a cell reference to the name and it bombs out (#REF), rightfully so, because when I ask it to "show calculation steps" it tells me the path/file/cell that it is looking to. If I search for any parts of the file path throughout the workbook, it comes up with nothing. For this name to be stored in this book, shouldn't I be able to see this path stored somewhere? Whether name manager (it isn't) or VB?

I really appreciate your help.

Brett

Mike's December, 31 2011 post on this other string provided the work around I needed.

First I run that script in a newly created tab in the "infected" workbook without saving the file. After running the script I can delete all the old hidden names which are now revealed in Name Manager. Before saving the file, I delete all traces of the macro, and when I save, Excel has no "memory" of the macro.

Brett