Excel VBA Named Range

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.

How to Create Named Ranges?

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 excelName Range In ExcelName range in Excel is a name given to a range for the future reference. To name a range, first select the range of data and then insert a table to the range, then put a name to the range from the name box on the left-hand side of the window.read more.

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 boxName BoxIn Excel, the name box is located on the left side of the window and is used to give a name to a table or a cell. The name is usually the row character followed by the column number, such as cell A1.read more 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.

How to Create Named Ranges using VBA Code?

Example #1

Have you ever thought of creating a named range using VBA CodeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more?

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.

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]: 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.

[Refers to]: 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.”

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.”

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.

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 objectUsing The RANGE ObjectRange is a property in VBA that helps specify a particular cell, a range of cells, a row, a column, or a three-dimensional range. In the context of the Excel worksheet, the VBA range object includes a single cell or multiple cells spread across various rows and columns.read more. 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.”

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.

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 editorThe VBA EditorThe Visual Basic for Applications Editor is a scripting interface. These scripts are primarily responsible for the creation and execution of macros in Microsoft software.read more, apply this code.

Sub NamedRanges_Example1()

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

End Sub

It will calculate the profit amount in the cell named “Profit.”

This article has been a guide to VBA Named Range. Here, we discuss how to create and use VBA Named ranges to select cells or range of cells in Excel, along with practical examples and a downloadable template. Below you can find some useful Excel VBA articles: –

  • VBA DoEventsGet ASCII Code using VBAUsedRange in VBAVBA Subscript Out of Range Error