VBA Named Range

When we work with a large amount of data to avoid referring to a particular cell or cell range, we usually create named ranges. It allows us to refer to the required cell range through the named range. In VBA, to create a named range, we have Add Name Function.

We can select a cell or range of cells and name it. Then, after naming the cells, we can refer to those cells by entering those defined names instead of the usual row or column references.

It is a walk-in-the-park job to create named ranges. The first thing we need to do is to identify the cells we want to create a name range in excel.

Look at the below image.

To arrive at the profit in the B4 cell, we have applied the formula B2 – B3.

It is a common thing everybody does. But, how about creating the names and applying the formula like "Sales– Cost."

Place a cursor on cell B2 > Go to the Name box and call it "Sales".

Place a cursor on the B3 cell and call it Cost.

Now, in the "Profit" column, we can refer to these names instead of cell references.

It is the basic thing about Named ranges.

Example #1

Have you ever thought of creating a named range using VBA Code?

Then, follow the below steps to create a named range.

Step 1: Define the variable as “Range.”

Code:

Sub NamedRanges_Example()

  Dim Rng As Range

End Sub

Step 2: Now, set the variable "Rng" to specific cells you wish to name.

Code:

Sub NamedRanges_Example()

  Dim Rng As Range

  Set Rng = Range("A2:A7")

End Sub

Step 3: Access the Names property using the "ThisWorkbook" object.

We have so many parameters with Names.Add method. Below are the explanations.

: Name is nothing, but what is the range name we would like to give? We specify.

While naming the cell, it should not contain any special characters except the underscore (_) symbol, and it should not contain space characters as well. In addition, it should not start with numerical values.

: This is nothing but what is the range of cells we are referring to.

We think these two parameters are good enough to start the proceedings.

Step 4: In the name, the argument enters the name you wish to give. For example, we have named it "SalesNumbers."

Code:

Sub NamedRanges_Example()

  Dim Rng As Range

  Set Rng = Range("A2:A7")

  ThisWorkbook.Names.Add Name:="SalesNumbers"

End Sub

Step 5: In refers to an argument, enter the range of cells we wish to create. For example, in the name of the "Rng" variable, we have already assigned the range of cells as A2 to A7. So, supply the argument as "Rng."

Code:

Sub NamedRanges_Example()

  Dim Rng As Range

  Set Rng = Range("A2:A7")

  ThisWorkbook.Names.Add Name:="SalesNumbers", RefersTo:=Rng

End Sub

This code will create a named range for cells from A2 to A7.

Now in the worksheet, we have created some numbers from A2 to A7.

In the A8 cell, we want to have the total of the above cell numbers. Then, we will create a SUM of these numbers using the Named range.

Code:

Sub NamedRanges_Example()

  Dim Rng As Range

  Set Rng = Range("A2:A7")

  ThisWorkbook.Names.Add Name:="SalesNumbers", RefersTo:=Rng

    Range("A8").Value = WorksheetFunction.Sum(Range("SalesNumbers"))

End Sub

Suppose you run this code manually or by pressing the F5 key. Then, we will get the total of a named range in cell A8.

These are the basic must-know facts about "Named Ranges."

Example #2

In vba, we can refer to the cells using the RANGE object. Similarly, we can also refer to those cells by using named ranges.

For example, in the above example, we have named the cell B2 as “Sales” and B3 as “Cost.”

By using actual cell references, we refer to those cells like this.

Code:

Sub NamedRanges()

 Range("B2").Select
 'This will select the B2 cell

 Range("B3").Select
 'This will select the B3 cell

End Sub

Since we already created these cells, we can refer to them using those names below.

Code:

Sub NamedRanges()

 Range("Sales").Select
 'This will select cell named as "Sales" i.e. B2 cell

 Range("Cost").Select
 'This will select cell named as "Cost" i.e. B3 cell

End Sub

Like this, using Named ranges, we can use those cells. For example, using these names, we can calculate the profit amount in cell B4. So for this first name, cell B4 is "Profit."

Now in the VBA editor, apply this code.

Code:

Sub NamedRanges_Example1()

    Range("Profit").Value = Range("Sales") - Range("Cost")

End Sub

It will calculate the profit amount in the cell named "Profit."