Choose your language

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:

DropDown Nomeados

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

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:

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

Intersection



SuperExcelVBA.com is learning website. Examples might be simplified to improve reading and basic understanding. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. All Rights Reserved.

Excel ® is a registered trademark of the Microsoft Corporation.

© 2019 SuperExcelVBA | ABOUT

Protected by Copyscape