How to Create Variable Range in VBA

This article will introduce how to set a variable in range in VBA Excel.

Set a Variable Range in VBA

The range is a VBA property used to select a single cell, a group of cells, rows, and columns. The range is widely used in VBA for selecting datasets from sheets and assigning them values or getting values from the dataset.

We must remember which range to use when using a range property. To overcome this problem, we can assign the range to a variable and use it later whenever we want to use it.

These variables are beneficial when dealing with complex programming and automation. Set statements can be used to fix a variable to a range of a specific size, and the range property method can be used to obtain values stored in that specific range or substitute them with some other values.

Let’s go through an example and try to assign a range to a variable.

Code:

# VBA
Sub VarRange()
Worksheets("Sheet1").Activate
Set ARange = Range("A1")
ARange.Value = "Selected"
End Sub

Output:

As you can see, we assigned cell A1 to the ARange variable. Now, let’s try to assign a range of multiple cells to a variable in another example.

Code:

# VBA
Sub VarRange()
Worksheets("Sheet1").Activate
Set ARange = Range("A1:B10")
ARange.Value = "Selected"
End Sub

Output:

From the above code, we assigned the value to all the cells present in that range. We can use this method to assign values to a certain row or a column.

We can also use the variable range to read the value of the range. Let’s try to read the cell’s value and display it in the message box.

Code:

# VBA
Sub VarRange()
Worksheets("Sheet1").Activate
Set ARange = Range("A4")
msgBox ARange
End Sub

Output: