How to use a Range or a Cell as a Variable in VBA

What is a Range Variable in VBA

In Excel VBA, a range variable is a variable but an object variable. Reason: VBA treats a range as an object. And just like any other variable, you can name the range variable to make it readable by the user. To use it in a code, you need to set the range to the variable after you declare it. And for this, you need to use the keyword SET.

Declare a Range Variable in VBA

To use a range or a single cell as a variable, first, you need to declare that variable with the range data type (object variable). Once you do that you need to specify a range of a cell to that variable using the range object. This also gives you access to all the properties and methods that you can use with a range.

1. First of all, use the Dim keyword and enter the name of the variable. In our example, we are using the name rng.
2. After that, enter the keyword, as to get the list of the data types and declare the data type as Range. And then type the name of the variable.
3. Now, enter the keyword Set to define the range to the variable.
4. Next, enter the name of the variable which you have declared in the previous line and type an equal (=) to sign.
5. In the end, enter the range object and define the address of the range which you want to assign to the variable.

Sub vba_range_variable()
Dim rng As Range
Set rng = Range("A1:A10")
End Sub

In the above code, the first line declares the variable and the second line of code, sets the range A1:A10 to the range variable.

[Example-1] Set Selection to Range Variable

To understand the range variable, let’s take a simple example. In VBA, when Selection property refers to the cell or a range you have selected, that means the currently selected range.

Dim rng As Range
Set rng = Selection

And like the above code, when you use the keywords Set, enter the name of the variable and then use the Selection property. The range variable rng, refers to the selected range.

Now, when you enter the name of the range variable and then enter a dot, it shows you a list of all the properties and methods that come with a range object. See the VBA code below, where we selected the clear method from the list to clear the selected range.

Sub vba_range_variable()
Dim rng As Range
Set rng = Selection
rng.Clear
End Sub

But there is one thing that you need to take care that when you are selecting an object other than a range, in that case VBA will show you an error (VBA Type Mismatch Error).

But this is also a dynamic method where you don’t need to change the range again and again. To create an actual dynamic range variable. you can check out the next example.

[Example-2] Copy a Range using Variable

Once you declare a variable as a range you can copy that range using that variable name as well. Let say you have declared the range A1:A10 as a variable rng, you can copy it using the following VBA code.

Sub vba_range_variable()
  Dim rng As Range
  Set rng = Range("A1:A10")
  rng.Copy
End Sub

Related: Copy and Paste in Excel using VBA

[Example-3] Using Range Variable in a Function

You can also use a range variable to refer to a range in a function while writing a macro. Consider the following code.

Sub vba_range_variable()
Dim iNames As Variant
Dim rng As Range
Set rng = Range("A1:A10")
Range("B1") = WorksheetFunction.Max(rng)
End Sub

It takes values from the range A1:A10 and as you have to use the variable “rng” we have used the variable in the to the max value to the from it.

Related: How to use WorksheetFunction in VBA

[Example-4] Count Number of Rows and Columns

As I have already said, once specify a variable as a range you will also get all the properties and methods to access. Imagine if you want to count the rows and columns. Following is the code that could be used.

Sub vba_range_variable()
Dim rng As Range
Set rng = Range("A1:A10")
MsgBox "This range has " & _
rng.Rows.Count & " row(s) and " & _
rng.Columns.Count & " coulmn(s)."
End Sub

When you run this code, it shows a message with the count of rows and columns from the range that you have specified in the variable.

[Example-5] Defining Worksheet Along with the Range

In all the examples above, we have used range without mentioning the worksheet, so when you run that code, VBA will always refer to the range in the active sheet. But if you want to refer to a specific worksheet then you need to use the worksheet object along with the range.

Dim rng As Range
Set rng = Worksheets("Sheet1").Range("A1:A10")

In the above code,

[Example-6] Using a Dynamic Range in the Range Variable

In the above example, the range address we have used is a fixed range (A1:A10). Now there might be a situation where you need to have a range address which requires to get update in the future. Well, for this case, you can write a VBA code can update the range.

In the below code, we have defined three variables, one if for the range and the rest two to get the last column and row.

Dim rng As Range
Dim iCol As Long
Dim iRow As Long

[Example-7] Using a Dynamic Range in the Range Variable

In the above example, the range address we have used is a fixed range (A1:A10). Now there might be a situation where you need to have a range address which requires to get update in the future. Well, for this case, you can write a VBA code can update the range.

In the below code, we have defined three variables, one if for the range and the rest two to get the last column and row.

Dim rng As Range
Dim iCol As Long
Dim iRow As Long

Now, you need to get the last row and column with the data. This helps the code to get the updated range every time you run the code.

iCol = Cells(1, Columns.Count).End(xlToLeft).Column
iRow = Cells(Rows.Count, 1).End(xlUp).Row

From here, you need to specify the range to the variable. And for this, we will use the same method which you have learned earlier. But, to define the range you need to use the CELLS and RESIZE property.

Set rng = Cells(1, 1).Resize(iRow, iCol)

In the above line of code, cells property tells VBA to refer to the first cell of the worksheet (i.e. A1) and then the resize property tell VBA to increase the range of according to the row and column number specified.

And with the iRow and iCol, you will get the number of row and column which has last cell with the data.