Name ranges are an exceptionally useful way to manage blocks of data on Excel workbooks. You can call them in functions thus reducing formulae size; you can use them in VBA code routines thus reducing script length. Moreover, if ranges are named sensibly, they allow users who aren’t familiar with the workbook to understand how complex calculations are derived more easily.
These name ranges are easy to create without accessing the menu. You simply highlight the relevant cells and use the Name Box in the top left of the screen (or the Name Manager short-cuts on the excel ribbon). However, to edit or delete the name, or modify the range on the respective worksheet, you must use the Name Manager menu. When the number of names in a workbook becomes large, this can be extremely cumbersome.
The requirement to edit or delete names occurs more often than you might think. You may misspell the name, or accidentally create multiple names for the same range. Furthermore, if you delete the worksheet of the range of the name, the name does not disappear from the list; in this case, you could have multiple names that do not refer to any actual range but appear in your list. Examples of these errors are below.
We show below how to use simple VBA to define, delete and edit collections of name ranges from a worksheet, thus removing the need to continually access the menu. We shall assume there is a worksheet called “NameList” in our workbook, and we have three columns in this worksheet similar to the above.
The variable EndList is used to indicate where the list stops (this is only for convenience) and the rowcounter variable increments the row for the output.
Listing name ranges
Here we simply write each name sequentially into the first two columns of the worksheet: the first column contains the name and the second its range. Note, we have to add a speechmark before the “range cell” to avoid Excel thinking that cell is a formula. Also note that RefersTo can be altered to RefersToR1C1 if (row, column) output is required. (We note that, this can be accomplished via the ribbon but for completeness we repeat it here.)
rowcounter=1
For Each nm In ActiveWorkbook.Names
Worksheets(“NameList”).Cells(rowcounter, 1).Value = nm.Name
Worksheets(“NameList”).Cells(rowcounter, 2).Value = “‘” & nm.RefersTo
rowcounter= rowcounter+1
Next nm
Deleting name ranges
Here we delete the names corresponding to the rows that have “Error” in the third column.
For rowcounter = 1 To EndList
If Worksheets(“NameList”).Cells(rowcounter, 3) = “Error” Then
nm= Worksheets(“NameList”).Cells(rowcounter, 1)
ActiveWorkbook.Names(nm).Delete
End If
Next rowcounter
Editing name references
Here we change the ranges of the names corresponding to the rows that have “Duplicate” in the third column. (Note we could have simply just changed all names if we had omitted the IF statement.)
For rowcounter = 1 To EndList
If Worksheets(“NameList”).Cells(rowcounter, 3) = “Duplicate” Then
nm = Worksheets(“NameList”).Cells(rowcounter, 1)
refer= Worksheets(“NameList”).Cells(rowcounter, 2)
ActiveWorkbook.Names(nm).Refersto =refer
End If
Next rowcounter
Adding new names
Here we add new names and ranges to the collection corresponding to the rows that have “New” in the third column. (Again, if we had omitted the IF statement then it would have added / overwritten all names in the list.)
For rowcounter = 1 To EndList
If Worksheets(“NameList”).Cells(rowcounter, 3) = “New” Then
nm = Worksheets(“NameList”).Cells(rowcounter, 1)
refer= Worksheets(“NameList”).Cells(rowcounter, 2)
ActiveWorkbook.Names.Add Name:=nm, Refersto:=refer
End If
Next rowcounter
