# Named Ranges
# Define A Named Range
Using named ranges allows you to describe the meaning of a cell(s) contents and use this defined name in place of an actual cell address.
For example, formula
=A5*B5 can be replaced with
=Width*Height to make the formula much easier to read and understand.
To define a new named range, select cell or cells to name and then type new name into the Name Box next to the formula bar.
Note: Named Ranges default to global scope meaning that they can be accessed from anywhere within the workbook. Older versions of Excel allow for duplicate names so care must be taken to prevent duplicate names of global scope otherwise results will be unpredictable. Use Name Manager from Formulas tab to change scope.
# Using Named Ranges in VBA
Create new named range called ‘MyRange’ assigned to cell
ThisWorkbook.Names.Add Name:="MyRange", _ RefersTo:=Worksheets("Sheet1").Range("A1")
Delete defined named range by name
Access Named Range by name
Dim rng As Range Set rng = ThisWorkbook.Worksheets("Sheet1").Range("MyRange") Call MsgBox("Width = " & rng.Value)
Access a Named Range with a Shortcut
Just like any other range, named ranges can be accessed directly with through a shortcut notation that does not require a
Range object to be created. The three lines from the code excerpt above can be replaced by a single line:
Call MsgBox("Width = " & [MyRange])
Note: The default property for a Range is its Value, so
[MyRange] is the same as
You can also call methods on the range. The following selects
Note: One caveat is that the shortcut notation does not work with words that are used elsewhere in the VBA library. For example, a range named `Width` would not be accessible as `[Width]` but would work as expected if accessed through `ThisWorkbook.Worksheets("Sheet1").Range("Width")`
# Manage Named Range(s) using Name Manager
Formulas tab > Defined Names group > Name Manager button
Named Manager allows you to:
- Create or change name
- Create or change cell reference
- Create or change scope
- Delete existing named range
Named Manager provides a useful quick look for broken links.
# Named Range Arrays
Sub Example() Dim wks As Worksheet Set wks = ThisWorkbook.Worksheets("Sheet1") Dim units As Range Set units = ThisWorkbook.Names("Units").RefersToRange Worksheets("Sheet1").Range("Year_Max").Value = WorksheetFunction.Max(units) Worksheets("Sheet1").Range("Year_Min").Value = WorksheetFunction.Min(units) End Sub