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

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 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 case of “Object Variables,” we need to set the reference.

For example, in this case, our variable (Rng) object is range so we need to set the reference 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 object 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 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

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