Excel/Delete a range name
Hi Damon. Over the years, I have established many range names using Excel 2010. I recently came across the "Name Manager" in the ribbon and find that many of my range names are #Ref. I deleted one or two but it is very time consuming. Is there any way to delete multiple range names at the same time?
I haven't used Excel 2010, having skipped from Excel 2007 directly to Excel 2013. However, I believe you can select multiple names from the list of all names in the Name Manager by holding down the Ctrl key as you select each name. Then you can delete them with one click of the delete button.
That being said, if you have a really lot of names the easiest way to delete them in bulk--or even particular ones that met certain naming rules--is using VBA. For example, to delete ALL names in your workbook you can run the following macro:
Pretty simple, huh?
But now say you only want to delete all the names beginning with the text "table". You could then have the code loop through the names, like this:
For Each n In Names
'just look at leftmost 5 characters of name
If Left(n.Name,5) = "table" Then n.Delete
This would delete tablexyz, table231 and tables4567, but not Xtable.
You can get even more sophisticated using the Like operator which allows the use of wildcard characters. Look up "Like" in the VBA helps to see the list of all the wildcard characters and examples of their use. Here is but one example. Say you want to delete all names that have "Cap" anyplace in the name.
For Each n In Names
'the * character matches any number of characters
If n.Name Like "*Cap*" Then n.Delete
For example this would delete the names:
Cap, nCap, xyzCap, abCap12345, but not aCarp123.
To try any of these macros, just go to the Visual Basic Editor (VBE) by entering keyboard Alt-TMV, then insert a new macro module by entering Alt-IM, then paste the code of any or all of these macros in the Code pane. Then go back to Excel and run the desired macro by entering Alt-TMM.
I hope you find this helpful.
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here