VBA Variable Range

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.

You are free to use this image on 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

For example, 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 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

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

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

Code:

Sub Range_Variable_Example()

  Range("A2:B10").Select

End Sub

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

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

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.

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.

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

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

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

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

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.

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

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