VBA Named Range

---

Naming ranges is a common practice for Excel users, and can be a convenient way to reference a set of cells.

Named ranges have some advantages:

• They can be used recurrently in VBA formulas and codes
• They allow to quickly navigate to the cells through the dropdown menu

• You don't lose the reference with the defined range even after adding rows and columns

Manually naming a range

1. Go to the Formulas tab
2. Click on Define Name
3. Enter a name for the range in the field Name:
4. Select the range of cells in the field Refers to:
5. Click OK

Naming a range with VBA

    Names.Add Name:="Sales", RefersTo:=Range("B3:E9")
    Names.Add Name:="Expenses", RefersTo:=Worksheets(1).Range("A2:F15")

In the above example, two named ranges were created:

• "Sales" referring to the range from "B3" to "E9"
• "Expenses" referring to the range from "A2" to "F15" for the worksheet of index 1

To access or modify the contents of a named range simply reference it in text format within Range:

    Range("Expenses").Value=0

In spite of the common use of named ranges, we recommend whenever possible the usage of Set for Range, for the speed and the simplification of the code.

---

---

Consolidating Your Learning

Suggested Exercise

---

• Previous page
• Next page

---

• report error/feedback