How to use a Range or a Cell as a 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. 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. Use the “Dim” keyword and enter the name of the variable.
2. Declare the keyword as Range.
3. Type the name of the variable.
4. Specify the range that you want to assign to the variable.

Set Selection to Range Variable

You can also use the selected range to a variable and then that variable to access properties and methods. Consider the following code.

Sub vba_range_variable()
  Dim rng As Range
  Set rng = Selection
  Selection.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.

But this is also a dynamic method where you don’t need to change the range again and again.

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 (above example), you can copy it using the following code.

Sub vba_range_variable()

  Dim rng As Range

  Set rng = Range("A1:A10")

  rng.Copy

End Sub

Related:

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

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.

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