VBA Variable Range

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.

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 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.

Examples of Range Variable in Excel VBA

Assume you want to select the range of cells from A2 to B10 for the below screenshot image.

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

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

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

Run the code. It will select the mentioned cells.

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."

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.

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.

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

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

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

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

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

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.

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

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 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: -

• Public Variables in VBA
• VBA Variable Types
• VBA Sort Range
• VBA Select Range