WallStreetMojo

WallStreetMojo

WallStreetMojo

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

VBA Variable Range

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

By Jeevan A Y

Excel VBA Variable in Range

Variables are the heart and soul of any big VBA projects since variables are heart and soul, then the kind of data type we assign to them is also a very important factor in that regard. In our many earlier articles, we have discussed plenty of times about variables and their data type importance. One such variable and data type is “Range Variable” in this special dedicated article. We will give a complete guide on “Range Variable” in excel VBA.

What is Range Variable in Excel VBA?

Like every other variable Range in VBA, the variable is also a variable, but it’s an “Object Variable” we use to set the reference of the specific range of cells.

Like any other variable, we can give any name to the variable, but the data type we assign to them should be a “Range.” Once the data type is assigned to the variable, it becomes an “Object Variable,” and unlike another variable, we cannot start using the variable before we set the reference of objects in case of object variables.

So, after we declare the variable, we need to use the “SET” keyword to set the object reference, i.e., Range object in this case.

Ok, now we will see some of the examples of excel VBA Range Variables practically.

VBA Variable Range

Examples of Range Variable in Excel VBA

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

For example, assume you want to select the range of cells from A2 to B10 for the below screenshot image.

VBA Variable Range - Example 1

To select this mentioned range of cells, all these while we have RANGE object, and inside the range object, we have mentioned the cell address in double-quotes.

Code:

Sub Range_Variable_Example()

  Range ("A2:B10")

End Sub

VBA Variable Range - Example 1-1

Once the range of cells is mentioned using RANGE object, if you put a dot, we would see all the properties and methods associated with this range object.

Code:

Sub Range_Variable_Example()

  Range ("A2:B10").

End Sub

VBA Variable Range - Example 1-2

Since we need to select the mentioned cells simple, choose the “Select” method from the IntelliSense list.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Code:

Sub Range_Variable_Example()

  Range("A2:B10").Select

End Sub

VBA Variable Range - Example 1-3

Run the code, and it will select the mentioned cells.

VBA Variable Range - Example 1-4

This is obvious, isn’t it, but imagine the scenario of using the same range in the long VBA project let’s say hundred times, writing the same code of “Range(“A2:A10”)” 100 times will take some time, but instead we will declare the variable and assign the data type as “Range” object.

Ok, let’s give your own name to a variable and assign the data type as “Range.”

VBA Variable Range - Example 1-5

Other than “Object Variables,” we can start using the variables by their name, but in the case of “Object Variables,” we need to set the reference.

For example, in this case, our variable (Rng) object is a range, so we need to set the reference to the word “Rng” is going to refer to. To set the reference, we need to use the “Set” keyword.

VBA Variable Range - Example 1-6

Now the variable “Rng” refers to the range of cells from A2 to B10. Instead of writing “Range(“A2:B10”))” every time, we can simply write the word “Rng.”

In the next line, mention the variable name “Rng” and put a dot to see the magic.

VBA Variable Range - Example 1-7

As you can see above, we can see all the properties and methods of range objects like the previous one.

Make the Variable Dynamic

Now we know how to set the reference to the range of cells, but once we mention the range of cells, it sticks to those cells only. Any addition or deletion of cells will not impact those cells.

So, finding the new range of cells after any addition or deletion of cells makes the variable dynamic in nature. This is possible by finding the last used row and column.

To find the last used row and column, we need to define two more variables.

Code:

Sub Range_Variable_Example()

  Dim Rng As Range
  Dim LR As Long 'LR = Last Row for Understanding
  Dim LC As Long 'LC = Last Column for Understanding

End Sub

Example 2

Now below code will find the last used row & column before we set the reference to a range object variable.

Code:

Sub Range_Variable_Example()

  Dim Rng As Range
  Dim LR As Long 'LR = Last Row for Understanding
  Dim LC As Long 'LC = Last Column for Understanding

  LR = Cells(Rows.Count, 1).End(xlUp).Row
  LC = Cells(1, Columns.Count).End(xlToLeft).Column

End Sub

Example 2-1

Now open the “Set” keyword statement.

Code:

Sub Range_Variable_Example()

  Dim Rng As Range
  Dim LR As Long 'LR = Last Row for Understanding
  Dim LC As Long 'LC = Last Column for Understanding

  LR = Cells(Rows.Count, 1).End(xlUp).Row
  LC = Cells(1, Columns.Count).End(xlToLeft).Column

  Set Rng =

End Sub

Example 2-2

Unlike the previous method, we use VBA CELLS properties this time.

Code:

Sub Range_Variable_Example()

  Dim Rng As Range
  Dim LR As Long 'LR = Last Row for Understanding
  Dim LC As Long 'LC = Last Column for Understanding

  LR = Cells(Rows.Count, 1).End(xlUp).Row
  LC = Cells(1, Columns.Count).End(xlToLeft).Column

  Set Rng = Cells(1, 1)

End Sub

Example 2-3

I have mentioned Cells(1,1), i.e., this refers to the first cell in the active sheet, but we need the data range reference, so use the “RESIZE” property and mention “last used row & column” variables.

Code:

Sub Range_Variable_Example()

  Dim Rng As Range
  Dim LR As Long 'LR = Last Row for Understanding
  Dim LC As Long 'LC = Last Column for Understanding

  LR = Cells(Rows.Count, 1).End(xlUp).Row
  LC = Cells(1, Columns.Count).End(xlToLeft).Column

  Set Rng = Cells(1, 1).Resize(LR, LC)

End Sub

Example 2-4

Now this will set the latest reference to the range object variable “Rng.” Next, mention the variable name and use the “Select” method.

Sub Range_Variable_Example()

  Dim Rng As Range
  Dim LR As Long 'LR = Last Row for Understanding
  Dim LC As Long 'LC = Last Column for Understanding

  LR = Cells(Rows.Count, 1).End(xlUp).Row
  LC = Cells(1, Columns.Count).End(xlToLeft).Column

  Set Rng = Cells(1, 1).Resize(LR, LC)

  Rng.Select

End Sub

Now I will add a few more lines to my data.

Example 2-5

I have added three extra lines of data. If I run the code now, it should select the latest data range.

Example 2-6

Things to Remember

  • The range variable in excel VBA is an object variable.
  • Whenever we use the object variable, we need to use the “Set” keyword and set the object reference to the variable.
  • Without setting the reference, we cannot use an object variable.

Recommended Articles

This has been a guide to VBA Variable Range. Here we discuss examples of range variables in excel VBA and how to makes the variable dynamic in nature. You can learn more about VBA functions from the following articles –

  • Public Variables in VBA
  • VBA Variable Types
  • VBA Sort Range
  • VBA Select Range
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Category iconExcel, VBA & Power BI,  Learn VBA

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 Variable Range Excel Template

Special Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More