Home » Excel, VBA & Power BI » VBA Resources » VBA Variable Range

VBA Variable Range

Article byWallstreetmojo Team
Reviewed byDheeraj Vaidya, CFA, FRM

Excel VBA Variable in Range

Variables are the heart and soul of any big VBA project. Since variables are heart and soul, the kind of data type we assign to them is also very important. 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 “Range Variable” guide in Excel VBA.

Table of contents

What is Range Variable in Excel VBA?

Like every other variable Range in VBARange In VBARange is a property in VBA that helps specify a particular cell, a range of cells, a row, a column, or a three-dimensional range. In the context of the Excel worksheet, the VBA range object includes a single cell or multiple cells spread across various rows and columns.read more, 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 we assign the data type to the variable, it becomes an “Object Variable.” Unlike another variable, we cannot start using the variable before we set the reference of objects in the 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.

Now, we will practically see some examples of Excel VBA Range variables.

VBA-Variable-Range

You are free to use this image o your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Variable Range (wallstreetmojo.com)

Examples of Range Variable in Excel VBA

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

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 a RANGE object. 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 we mention the range of cells using the RANGE object, if you put a dot, we will 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, choose the “Select” method from the IntelliSense list.

Code:

Sub Range_Variable_Example()

  Range("A2:B10").Select

End Sub
VBA Variable Range - Example 1-3

Run the code. It will select the mentioned cells.

VBA Variable Range - Example 1-4

It is obvious. 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. So instead, we will declare the variable and assign the data type as a “Range” object.

Let’s give your 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” it is going to refer to. Therefore, we need to use the “Set” keyword to set the reference.

VBA Variable Range - Example 1-6

The variable “Rng” refers to the range of cells from A2 to B10. So, instead of writing “Range(“A2:B10″))” every time, we can 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. So, 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. It is possible by finding the last used row and column.

We need to define two more variables to find the last used row and column.

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

Below code will find the last used row and 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 propertiesUse VBA CELLS PropertiesCells are cells of the worksheet, and in VBA, when we refer to cells as a range property, we refer to the same cells. In VBA concepts, cells are also the same, no different from normal excel cells.read more 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

We have mentioned Cells(1,1), i.e., the first cell in the active sheet. But we need the data range reference, so use the “RESIZE” property and mention the “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

It 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, we will add a few more lines to my data.

Example 2-5

We have added three extra lines of data. Therefore, the code should select the latest data range if we run the code now.

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.

This article is a guide to VBA Variable Range. Here, we discuss examples of range variables in Excel VBA and how to make the variable dynamic in nature. You can learn more about VBA functions from the following articles: –