EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login

VBA Named Range

By Dheeraj VaidyaDheeraj Vaidya

Home » VBA » Blog » VBA Lookup & Reference Functions » VBA Named Range

VBA Named Range

Overview on Excel VBA Named Range

In excel we learned how to name a range and use it. Now we will learn how to use this name range in VBA and in addition to that we will also learn how to name a range from VBA. Range is a crucial part of a worksheet as it contains the data for us. Specifically giving names to ranges help us to identify which part of the range contains which data.

Let us begin with how can we name a range in excel and then use it in VBA. In order to name a group of ranges in excel, we simply select the ranges as follows,

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

VBA Named Range 1

Now there is a box where it is mentioned as A1 we click on it and give it a name.

VBA Named Range 2

The box is called a name box and we use it to give a name to our range. For the current example, I have given it as a named range. Now once we have given the range name how do we use it in VBA. Suppose we had to change the interior color of the above range previously in VBA what we did in VBA is we referred to the range as follows,

Worksheets(“Sheet1”).Range(“A1:B5”).Interior.Color = Some Color code

Now since we have given the range a name we can now do the following to change the color of the range as follows,

Worksheets(“Sheet1”).Range(“NEW”).Interior.Color = Some Color code

NEW is the range name we have given to cell selection A1:B5. This is how we use name ranges in VBA once we have named them.

Popular Course in this category
VBA Training (3 Courses, 12+ Projects)3 Online Courses | 13 Hands-on Projects | 45+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.7 (7,159 ratings)
Course Price

View Course

Related Courses

Now we can also name the ranges using VBA too well it is a bit lengthy process than above but let us go through it.

To name a range in a worksheet by VBA we use the following code as follows,

Set myWorksheet = ThisWorkbook.Worksheets("Named Range")
Set myNamedRangeWorksheet = myWorksheet.Range("Range Value”)

How to Use Named Range in Excel VBA?

There are two methods to use the Named Range in Excel VBA:

  1. First is that we name a range in excel and then use it in VBA.
  2. The second method is we make a named range in VBA itself and use its properties.

Below are the different examples to use the Named Range in Excel:

You can download this VBA Named Range Excel Template here – VBA Named Range Excel Template

Example #1

For the first example let us use the name range we used above by making it in excel worksheet. Follow the below steps:

Step 1: Select the range which we want to name first as follows,

Example 1-1

Step 2: There is a name box above the range we use that to give a name to the range, for this example, I have named the ranged as New.

Example 1-2

Step 3: Now let us go to VBA, click on Developer’s tab to click on Visual Basic to get into VBA.

VBA Named Range Example 1-3

Step 4: Once we are in VBA we need to insert a module so that we can write code in it. Do as follows,

VBA Named Range Example 1-4

Step 5: Now let us start writing the code, to do that we need to name the macro first as follows,

Code:

Sub Sample()

End Sub

VBA Named Range Example 1-5

Step 6: Write the following code to activate the worksheet first in order to use the name range.

Code:

Sub Sample()

Worksheets("Sheet1").Activate

End Sub

VBA Named Range Example 1-6

Step 7: Now let us use our name range as follows,

Code:

Sub Sample()

Worksheets("Sheet1").Activate
Range("NEW").Value = 10

End Sub

VBA Named Range Example 1-7

Step 8: Once we run the above code we can see that every cell in our name range has the same value as follows,

Result of Example 1-8

Step 9: Let us also check if we can change the properties of the cell, Let us change the color of the range by using the name range as follows,

Code:

Sub Sample()

Worksheets("Sheet1").Activate
Range("NEW").Value = 10
Range("NEW").Interior.Color = 255

End Sub

VBA Named Range Example 1-9

Step 10: Run the above code by pressing F5 or from the run button and see the following result,

Result of Example 1-10

Example #2

We will first create a named range by using VBA and the use the properties of the name range in VBA itself. Follow the below steps:

Step 1: Now let us start writing the code, to do that we need to name the macro first as follows,

Code:

Sub Sample1()

End Sub

Example 2-1

Step 2: Declare a variable which will store the name of the name range as follows,

Code:

Sub Sample1()

Dim myRangeName As String

End Sub

VBA Named Range Example 2-2

Step 3: Now let us set the name range as follows, in this example whatever we have selected as the range will be our named range,

Code:

Sub Sample1()

Dim myRangeName As String
myRangeName = "namedRangeFromSelection"

End Sub

VBA Named Range Example 2-3

This is what I have in selection for named range cell range A1:C5 in worksheet 2,

VBA Named Range Example 2-4

Step 4: Now let us name the range by the following code,

Code:

Sub Sample1()

Dim myRangeName As String
myRangeName = "namedRangeFromSelection"
ThisWorkbook.Names.Add Name:=myRangeName, RefersTo:=Selection

End Sub

VBA Named Range Example 2-5

Step 5: Run the above code we can see that we have created the name range in worksheet 2 by the name of namedrangefromselection,

Result of Example 2-6

Step 6: Now let us use this name ranged as we used in the earlier example as follows,

Code:

Sub Sample1()

Dim myRangeName As String
myRangeName = "namedRangeFromSelection"
ThisWorkbook.Names.Add Name:=myRangeName, RefersTo:=Selection
Range("namedRangeFromSelection").Value = 10
Range("namedRangeFromSelection").Interior.Color = 255

End Sub

VBA Named Range Example 2-7

Step 7: Run the above code from the F5 button and see the result as follows,

Example 2-8

Things to Remember

  • Named Ranges are the name given for a group or range of cells.
  • Named range is used to refer to the specific range of cells which needs to be accessed later on.
  • We can name a range in both excel or VBA.
  • While making a named range in VBA we need to use Add name function in VBA.

Recommended Articles

This is a guide to VBA Named Range. Here we discuss how to use Named Range in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. Step by Step Guide to VBA Value
  2. How to Edit Drop Down List in Excel?
  3. VBA Subscript out of Range
  4. Import Data into Excel

VBA Training (3 Courses, 12+ Projects)

3 Online Courses

13 Hands-on Projects

45+ Hours

Verifiable Certificate of Completion

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
VBA Lookup & Reference Functions
  • VBA Lookup & Reference
    • VBA Selecting Range
    • VBA Range
    • VBA Match
    • VBA Transpose
    • VBA Delete Row
    • VBA Range Cells
    • VBA Delete Column
    • VBA Union
    • VBA Insert Column
    • VBA Named Range
    • VBA Hyperlink
    • VBA Last Row
    • VBA Name
    • VBA OFFSET
    • VBA Hide Columns
    • VBA Selection Range
    • VBA Columns
    • VBA Insert Row
    • VBA LOOKUP
    • VBA VLOOKUP Function
    • VBA Resize
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

© 2020 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA Login

Forgot Password?

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

*Please provide your correct email id. Login details for this Free course will be emailed to you
Book Your One Instructor : One Learner Free Class

Let’s Get Started

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

EDUCBA

Download VBA Named Range Excel Template

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

*Please provide your correct email id. Login details for this Free course will be emailed to you

Special Offer - VBA Training (3 Courses, 12+ Projects) Learn More