How to Use Named Range in Excel VBA (2 Ways)

Many a time while working with VBA in Excel, we have to use a Named Range. In this article, I’ll show you how you can create and refer to a Named Range with VBA in Excel with proper examples and illustrations.

Named Range in Excel VBA (Quick View)

It assigns the Named Range “Dataset” of the worksheet “Sheet1” and of the workbook “VBA Named Range” to the Range object called Rng.

Use Named Range in Excel VBA: 2 Simple Ways

A Named Range is a group of cells that has been provided with a specific name in an Excel worksheet.

You can create a Named Range manually by first selecting the desired range, then going to Formulas > Define Name > Define Name tool in the Excel toolbar.

Today our objective is to learn to create and refer to a Named Range with Visual Basic Application (VBA) in Excel.

1. How to Create a Named Range in Excel VBA

First, we’ll see how to create a Named Range with VBA.

To create a Named Range, first, you have to assign the desired range to a Range object.

Here I have assigned the range B3:D13 to a Range object called Rng.

Or if the range is of another worksheet other than the active one, you have to put the name of the worksheet before.

Here I’ve put the name of the worksheet “Sheet1” before.

Then you have to assign it as a Named Range. I have named it “Dataset”.

Or you can put the name of the workbook other than ThisWorkbook, if the workbook isn’t active.

Here I’ve put the name of the workbook “VBA Named Range.xlsm” in the front.

So the complete VBA code will be:

⧭ VBA Code:

Sub Create_Named_Range()

Set Rng = Worksheets("Sheet1").Range("B3:D13")

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

End Sub

⧭ Output:

Run the Macro (Create_Named_Range) and you’ll find the range B3:D13 assigned with the name Dataset.

Read More: Excel VBA to Create Named Range from Selection

2. How to Refer to a Named Range in Excel VBA

Now we’ll see how we can refer to a Named Range with VBA.

To refer to a Named Range, you have to enter the name within a Range object in VBA.

For example, to access the Named Range “Dataset”, we have to use:

Or you can use the name of the worksheet in the front if it’s not the active one.

Here it’s Sheet1.

You can put the name of the workbook in front also.

Here it’s VBA Named Range.xlsm.

Now, the Named Range “Dataset” is assigned to the Range object called Rng.

You can use any property available of the Range object.

For example, you can use the Select property of the Range object.

Run the code. And it’ll select the Named Range “Dataset”.

Or you can use the Cells property of the Range object of VBA.

It will show the value of the cell (2,1) of the Named Range “DataSet”, A Tale of Two Cities.

In this way, you can access all the properties of the Range object of VBA with the Named Range.

Read More: Excel Reference Named Range in Another Sheet

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

Using these methods, you can create and refer to a Named Range with VBA. Do you have any questions? Feel free to ask us.

Further Readings

• How to Name a Column in Excel
• How to Name a Group of Cells in Excel
• How to Find a Named Range in Excel
• How to Display Named Range Contents in Excel
• How to Paste Range Names in Excel
• How to Navigate to a Named Range in Excel
• How to Change Scope of Named Range in Excel
• How to Edit Named Range in Excel
• How to Create Dynamic Named Range in Excel
• Create Dynamic Named Range with VBA in Excel
• How to Use Dynamic Named Range in an Excel Chart
• How to Use Named Range in Excel VLOOKUP Function
• Excel INDIRECT Function with Named Range
• How to Ignore Blank Cells in Named Range in Excel
• How to Delete Named Range Excel
• How to Delete All Named Ranges in Excel
• Define Names with the Create from Selection Tool in Excel
• How to Set Value to a Named Range with Excel VBA
• Excel VBA to Loop Through Named Range
• How to Check If Named Range Exists with Excel VBA
• Excel VBA to Delete Named Range