EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home VBA VBA Resources VBA Lookup & Reference Functions VBA Named Range
 

VBA Named Range

Madhuri Thakur
Article byMadhuri Thakur

Updated April 10, 2023

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.

Watch our Demo Courses and Videos

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

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,

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.

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA

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

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

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, Web Development & many more.

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

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

EDUCBA

Download VBA Named Range Excel Template

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW