The VBA Guide To Named Ranges
What Is A Named Range?
Creating a named range allows you to refer to a cell or group of cells with a custom name instead of the usual column/row reference. The HUGE benefit to using Named Ranges is it adds the ability to describe the data inside your cells. Let’s look at a quick example:
Can you tell if shipping costs are charged with the product price?
- = (B7 + B5 * C4) * (1 + A3)
- =(ShippingCharge + ProductPrice * Quantity) * (1 + TaxRate)
Hopefully, you can clearly see option number TWO gives you immediate insight to whether the cost of the products includes shipping costs. This allows the user to easily understand how the formula is calculating without having to waste time searching through cells to figure out what is what.
How Do I Use Named Ranges?
As a financial analyst, I play around with a bunch of rates. Examples could be anything from a tax rate to an estimated inflation rate. I use named ranges to organize my variables that either are changed infrequently (ie Month or Year) or something that will be static for a good amount of time (ie inflation rate). Here are a list of common names I use on a regular basis:
- ReportDate
- Year
- Month
- FcstID
- TaxRate
- RawData
Creating Unique Names On The Fly
It is super easy to create a Named Range. All you have to do is highlight the cell(s) you want to reference and give it a name in the Name Box. You name cannot have any spaces in it, so if you need to separate words you can either capitalize the beginning of each new word or use an underscore (_). Make sure you hit the ENTER key after you have finished typing the name to confirm the creation of the Named Range.
As a side note, any Named Range created with the Name Box has a Workbook scope. This means the named range can be accessed by any worksheet in your Excel file.
Creating Names With The “Name Manager”
If you want to customize your named ranges even more, you can open up the Name Manager (Formulas tab > Defined Names group > Name Manager button) to edit and create new named ranges.
I won’t go into great detail in this article, but know that with the Name Manager you can
- Change the name of an existing Named Range
- Change the reference formula
- Specify the scope (what worksheets the name can be accessed from)
On To The VBA
Now that you have had a brief overview on Named Ranges, lets dig into some VBA macros you can use to help automate the use of Named Ranges.
Add A Named Range
The below VBA code shows ways you can create various types of named ranges.
Sub NameRange_Add()
'PURPOSE: Various ways to create a Named Range
'SOURCE: www.TheSpreadsheetGuru.com
Dim cell As Range
Dim rng As Range
Dim RangeName As String
Dim CellName As String
'Single Cell Reference (Workbook Scope)
RangeName = "Price"
CellName = "D7"
Set cell = Worksheets("Sheet1").Range(CellName)
ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell
'Single Cell Reference (Worksheet Scope)
RangeName = "Year"
CellName = "A2"
Set cell = Worksheets("Sheet1").Range(CellName)
Worksheets("Sheet1").Names.Add Name:=RangeName, RefersTo:=cell
'Range of Cells Reference (Workbook Scope)
RangeName = "myData"
CellName = "F9:J18"
Set cell = Worksheets("Sheet1").Range(CellName)
ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell
'Secret Named Range (doesn't show up in Name Manager)
RangeName = "Username"
CellName = "L45"
Set cell = Worksheets("Sheet1").Range(CellName)
ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell, Visible:=False
End SubLoop Through Named Ranges
This VBA macro code shows how you can cycle through the named ranges within your spreadsheet.
Sub NamedRange_Loop()
'PURPOSE: Delete all Named Ranges in the Active Workbook
'SOURCE: www.TheSpreadsheetGuru.com
Dim nm As Name
'Loop through each named range in workbook
For Each nm In ActiveWorkbook.Names
Debug.Print nm.Name, nm.RefersTo
Next nm
'Loop through each named range scoped to a specific worksheet
For Each nm In Worksheets("Sheet1").Names
Debug.Print nm.Name, nm.RefersTo
Next nm
End SubDelete All Named Ranges
If you need to clean up a bunch of junk named ranges, this VBA code will let you do it.
Sub NamedRange_DeleteAll()
'PURPOSE: Delete all Named Ranges in the ActiveWorkbook (Print Areas optional)
'SOURCE: www.TheSpreadsheetGuru.com
Dim nm As Name
Dim DeleteCount As Long
'Delete PrintAreas as well?
UserAnswer = MsgBox("Do you want to skip over Print Areas?", vbYesNoCancel)
If UserAnswer = vbYes Then SkipPrintAreas = True
If UserAnswer = vbCancel Then Exit Sub
'Error Handler in case Delete Function Errors out
On Error GoTo Skip
'Loop through each name and delete
For Each nm In ActiveWorkbook.Names
If SkipPrintAreas = True And Right(nm.Name, 10) = "Print_Area" Then GoTo Skip
'Error Handler in case Delete Function Errors out
On Error GoTo Skip
'Delete Named Range
nm.Delete
DeleteCount = DeleteCount + 1
Skip:
'Reset Error Handler
On Error GoTo 0
Next
'Report Result
If DeleteCount = 1 Then
MsgBox "[1] name was removed from this workbook."
Else
MsgBox "[" & DeleteCount & "] names were removed from this workbook."
End If
End SubDelete Named Ranges with Error References
This VBA code will delete only Named Ranges with errors in them. These errors can be caused by worksheets being deleted or rows/columns being deleted.
Sub NamedRange_DeleteErrors()
'PURPOSE: Delete all Named Ranges with #REF error in the ActiveWorkbook
'SOURCE: www.TheSpreadsheetGuru.com
Dim nm As Name
Dim DeleteCount As Long
'Loop through each name and delete
For Each nm In ActiveWorkbook.Names
If InStr(1, nm.RefersTo, "#REF!") > 0 Then
'Error Handler in case Delete Function Errors out
On Error GoTo Skip
'Delete Named Range
nm.Delete
DeleteCount = DeleteCount + 1
End If
Skip:
'Reset Error Handler
On Error GoTo 0
Next
'Report Result
If DeleteCount = 1 Then
MsgBox "[1] errorant name was removed from this workbook."
Else
MsgBox "[" & DeleteCount & "] errorant names were removed from this workbook."
End If
End SubAnything Missing From This Guide?
Let me know if you have any ideas for other useful VBA macros concerning Named Ranges. Or better yet, share with me your own macros and I can add them to the article for everyone else to see! I look forward to reading your comments below.
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.