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:
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 Sub
Loop 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 Sub
Delete 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 " name was removed from this workbook." Else MsgBox "[" & DeleteCount & "] names were removed from this workbook." End If End Sub
Delete 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 " errorant name was removed from this workbook." Else MsgBox "[" & DeleteCount & "] errorant names were removed from this workbook." End If End Sub
Anything 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!
5 Ways to Create A Dynamic Auto-Adjusting VBA Range
Dynamic Code Is Vital! A huge turning point for me when I was teaching myself how to write VBA was...
VBA To Determine If Duplicates Exist In Range
What This VBA Code Does There may be instances where you need to ensure there are no duplicate values within...
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.