• Paul Brown, Director

Name ranges in VBA

Updated: Sep 13, 2018


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


Author: Paul Brown, Director

22 views

ABOUT US

We solve the data problems that seem unsolvable.

GET SOCIAL

  • Jump Data Twitter Account
  • Jump Data Linkedin Account

Jumpdata Limited. Company No. 08241266.

JumpData Logo
Jump Data Linkedin Account
Jump Data Twitter Account