WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Named Range

VBA Named Range

Excel VBA Named Range

When we work with a large amount of data to avoid referring to a particular cell or cell ranges we usually create named ranges and it allows us to refer to the required cell range through the named range. In VBA to create name range we have Add Name Function.

We can select a cell or range of cells and give a name to it. After naming the cells, we can refer to those cells by entering those defined names instead of a usual row or column references.

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

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 name range in excel.

For example, look at the below image.

VBA Named Range Example 1

To arrive at the profit in B4 cell, I have applied the formula B2 – B3.

This is the common thing everybody does. But how about creating the Names and apply the formula something like “Sales” – “Cost.”

Place a cursor on the cell B2 > Go to Name box and call it as Sales.

VBA Named Range Example 1-1

Place a cursor on the B3 cell and call it Cost.

VBA Named Range Example 1-2

Now in the profit column, we can refer these names instead of cell references.

VBA Named Range Example 1-3

This 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 Code?

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

VBA Named Range Example 2.png

Step 2: Now set the variable “Rng” to specific cells you wish to name.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Code:

Sub NamedRanges_Example()

  Dim Rng As Range

  Set Rng = Range("A2:A7")

End Sub

VBA Named Range Example 2-1.png

Step 3: Using the “ThisWorkbook” object access Names Property.

VBA Named Range Example 2-2

We have so many parameters with Names.Add method. Below are the explanations.

[Name]: Name is nothing, but what is the name we would like to give to the range 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. It should not start with numerical values.

[Refers to]: This is nothing but what is the range of cells we are referring to.

I think these two parameters are good enough to start off the proceedings.

Step 4: In the name, the argument enters the name you wish to give. I have named as “SalesNumbers.”

Code:

Sub NamedRanges_Example()

  Dim Rng As Range

  Set Rng = Range("A2:A7")

  ThisWorkbook.Names.Add Name:="SalesNumbers"

End Sub

VBA Named Range Example 2-3

Step 5: In refers to an argument, enter the range of cells we wish to create. 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.”

Code:

Sub NamedRanges_Example()

  Dim Rng As Range

  Set Rng = Range("A2:A7")

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

End Sub

VBA Named Range Example 2-4

Ok, this code will create a named range for cells from A2 to A7.

Now in the worksheet, I have created some numbers from A2 to A7.

VBA Named Range Example 2-5

In the A8 cell, I want to have the total of the above cell numbers. Using the named range, we will create a SUM of these numbers.

Code:

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

create name Example 2-6

If you run this code manually or by pressing the f5 key then, we will get the total of a named range in cell A8.

Create Name Example 2-7

This is the basic must-know facts about “Named Ranges.”

Example #2

In VBA using RANGE object, we can refer to the cells. Similarly, we can also refer to those cells by using named ranges as well.

For example, in the above example, we have named the cell B2 as “Sales” and B3 as “Cost.”

By using actual cell reference, we refer to those cells like this.

Code:

Sub NamedRanges()

 Range("B2").Select
 'This will select the B2 cell

 Range("B3").Select
 'This will select the B3 cell

End Sub

VBA NR Example 1-4

Since we already created these cells, we can refer to using those names like below.

Code:

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

VBA NR Example 1-5

Like this, using Named Ranges, we can make use of those cells. Using these names, we can calculate the profit amount in cell B4. For this first name, the cell B4 as Profit.

VBA NR Example 1-6

Now in the VBA editor, apply this code.

Code:

Sub NamedRanges_Example1()

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

End Sub

VBA NR Example 1-7

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

VBA NR Example 1-8

Recommended Articles

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

  • VBA DoEvents
  • Get ASCII Code using VBA
  • UsedRange in VBA
  • VBA Subscript Out of Range Error
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* 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
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

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

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

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

Login

Forgot Password?

WallStreetMojo

Download VBA Named Range Excel Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More