Clear Names From Excel

Q: I use Excel 2003 for tracking and managing my investments. There are separate worksheets for groups of stocks I own or have owned. The active holdings are linked to a single summary page. Over the years, I have somehow accumulated a long list of named ranges that were applicable once upon a time but no longer. When I move a sheet to another file (for example after selling a stock), I get an error message that such-and-such a name is duplicated on the sheet - what do I want to do. When I look at the defined range names list, there are many. I can go through each and delete each one separately. This takes time and is cumbersome. Worse, the next time I move a worksheet I get the same message again! Is there a way to do a wholesale housecleaning and get rid of all names in all worksheets? - Wayne Howard.





A: If a worksheet contains only data, no formulas, there's an easy way to strip out unwanted names, along with formatting, macros and anything other than the data. Save the worksheet as a .csv file, close it, re-open it, and save it as an .xls file with a new name - don't overwrite the original. This is the equivalent of saving a Word document as plain text. However, it's only good for one worksheet at a time and you mention having workbooks with many sheets.
In your case a simple macro will be a better solution. Select Tools | Macro | Macros from the menu, type a name for your macro, and click the Create button. Let's call it NoNames. Type in the three middle lines (Excel supplies the first and last) to make it look like this:
Sub NoNames()