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.

Download Practice Workbook

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

2 Simple Ways to Use Named Range in Excel VBA

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 (5 Examples)

Similar Readings

• How to Delete All Named Ranges in Excel (2 Ways)
• Use Dynamic Named Range in an Excel Chart (A Complete Guide)
• How to Name a Group of Cells in Excel (3 Methods +1 Bonus)
• Change Scope of Named Range in Excel (3 Methods)
• How to Paste Range Names in Excel (7 Ways)

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: How to Edit Named Range in Excel

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 Remove Named Range in Excel (4 Quick Methods)
• Dynamic Named in Range Excel (Both One and Two Dimensional)
• How to Delete Named Range Excel (3 Methods)
• [Fixed!] Name Manager Delete Option Greyed out in Excel (3 Reasons)
• How to Set Value to a Named Range with Excel VBA (3 Methods)