You are here:

Excel/Delete a range name

Advertisement


Question
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?

Answer
Hi Robert,

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:

Sub DelAllNames()
  Names.Delete
End Sub

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:

Sub DelNames()
  For Each n In Names
     'just look at leftmost 5 characters of name
     If Left(n.Name,5) = "table" Then n.Delete
  Next n
End Sub

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.

Sub DelNames2()
  For Each n In Names
     'the * character matches any number of characters
     If n.Name Like "*Cap*" Then n.Delete
  Next n
End Sub

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.

Damon
About Excel
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

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Damon Ostrander

Expertise

I have extensive experience with VBA programming in Excel 5 through Excel 2013. As a former aerospace engineer with a large aerospace corporation and consultant in a small defense technology services company, I have developed a wide range of applications in VBA, including simulations involving mixed-language programming, satellite orbit mechanics, graphics and animation, and real-time applications. I am interested in moderate to hard VBA-related questions only.

Experience

I have developed and taught several courses in Excel VBA programming and also VBA programming in Office 97, 2000, and 2007. I have developed a number of large technical applications in Excel VBA for use within the aerospace industry.

Education/Credentials
B.S. in Electrical Engineering and Computer Science, University of California, Berkeley.

©2016 About.com. All rights reserved.