Excel Guides

Using Named Ranges in a Macro in Excel

When working with macros in Excel, named ranges can be extremely helpful in making your code more readable and easier to maintain. By using named ranges, you can make your code more self-documenting and avoid hard-coding cell references.

There are two ways to create a named range in Excel:

  1. Select the cells you want to name, then click Insert > Name > Define. In the Name box, type a name for the range, then click OK.
  2. Click the cell where you want the upper-left corner of the range to appear, then click Insert > Name > Create. In the Refers to box, type =RANGE_NAME (for example, =MyRange), then click OK.

Naming a range

Sub Test()

    Dim myRange As Range

    ' Use a named range.
    Set myRange = Range("MyRange")

    ' Use an absolute cell reference.
    Set myRange = Range("A1:B10")

    ' Use a relative cell reference.
    Set myRange = Range("A1", Range("B1"))

    ' Use an intersection of two named ranges.
    Set myRange = Range("MyRange1").Intersect(Range("MyRange2"))

    ' Use a union of two named ranges.
    Set myRange = Union(Range("MyRange1"), Range("MyRange2"))

    ' Use an array constant.
    Set myRange = Range(Array("A1", "B1", "C1", "D1"))

   End Sub 

Using a named range in a formula

Sub Test()

   Dim myCell As Range

   ' Use a named range in a formula.
   Set myCell = Range("A1")
   myCell.Formula = "=SUM(MyRange)"

   End Sub 

Referring to a named range from another worksheet

Sub Test()

   Dim myCell As Range

   ' Refer to a named range on another worksheet.
   Set myCell = Worksheets("Sheet2").Range("A1")

   End Sub 

Referring to a named range from another workbook

Sub Test()

   Dim myCell As Range

   ' Refer to a named range in another workbook.
   Set myCell = Workbooks("Workbook2").Worksheets("Sheet1").Range("A1")

   End Sub 

Deleting a named range

Sub Test()

   Dim myName As Name

   ' Delete a named range.
   For Each myName In ActiveWorkbook.Names
       If myName.Name Like "My*" Then  ' Change My* to match your naming convention.
           ActiveWorkbook.Names(myName).Delete     ' or use .Delete shift:=xlUp/xlToLeft/etc... as desired 									      (see help) 				     end if  Next  End Sub 

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.