Declare the variable As Range, then assign it with Set: Dim rng As Range, then Set rng = Worksheets("Data").Range("A2:D100"). After that, rng works anywhere a range does, as in rng.Font.Bold = True or WorksheetFunction.Sum(rng).
Leaving out Set doesn’t assign the range. rng = Range("A1") tries to write A1’s value into the empty variable and stops with error 91. Include the sheet when you set it, and the variable keeps pointing at that sheet whichever one is active later.
To size the range to the data, set it from the last row and column:
Dim ws As Worksheet, rng As Range, lastRow As Long, lastCol As Long
Set ws = Worksheets("Data")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
Set rng = ws.Range("A1").Resize(lastRow, lastCol)
A range variable can hold one cell or many: rng.Rows.Count and rng.Columns.Count give its size, and rng.Address its address. After Find or Intersect, check If rng Is Nothing Then, because both leave the variable empty when nothing matches.
What is a Range Variable in VBA
In Excel VBA, a range variable is not a regular 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 assign a range or 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.

- First of all, use the
Dimkeyword and enter the name of the variable. In our example, we are using the namerng. - After that, enter the keyword As to get the list of the
data typesand declare the data type as Range. - Now, enter the keyword
Setto define the range to the variable. - Next, enter the name of the variable which you have declared in the previous line and type an equal (=) sign.
- In the end, enter the
rangeobject 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 Dim line declares the variable, and the Set line assigns 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, the Selection property refers to the cell or range you have selected, which means the currently selected range.
Dim rng As Range Set rng = Selection
And like the above code, when you use the keyword 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 of: when you have selected an object other than a range (like a chart or a shape), VBA will show you an error (VBA Type Mismatch Error).

This is also a dynamic method, as you don’t need to change the range again and again. To create an actual dynamic range variable, you can check out the dynamic range example below.
[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’s 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 the values from the range A1:A10, and as you have stored that range in the variable “rng”, we have used the variable in the MAX function to get the maximum value from it and enter it in cell B1.
Related: How to use WorksheetFunction in VBA
[Example-4] Count Number of Rows and Columns
As I have already said, once you specify a variable as a range, you also get access to all its properties and methods. Imagine you want to count the rows and columns. Following is the code that you can use.
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, the range variable refers to A1:A10 on “Sheet1”, whichever sheet is active when you run the 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 needs to be updated in the future. Well, for this case, you can write a VBA code that updates the range.
In the below code, we have defined three variables: one is for the range, and the other two are 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 needs to be updated in the future. Well, for this case, you can write a VBA code that updates the range.
In the below code, we have defined three variables: one is for the range, and the other two are 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 properties.
Set rng = Cells(1, 1).Resize(iRow, iCol)
In the above line of code, the Cells property tells VBA to refer to the first cell of the worksheet (i.e. A1), and then the Resize property tells VBA to resize the range according to the row and column numbers specified.
And with iRow and iCol, you will get the numbers of the last row and column with data.
Here’s the full code with all the parts together. In the end, it selects the range, so you can see which cells the variable refers to:
Sub vba_dynamic_range_variable()
Dim rng As Range
Dim iCol As Long
Dim iRow As Long
iCol = Cells(1, Columns.Count).End(xlToLeft).Column
iRow = Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Cells(1, 1).Resize(iRow, iCol)
rng.Select
End Sub
Loop Through Each Cell in a Range Variable
A range variable works well with a For Each loop. You need a second range variable for the cell that the loop is on. The following code adds 10 to every number in A1:A10:
Sub loop_range_variable()
Dim rng As Range
Dim cell As Range
Set rng = Range("A1:A10")
For Each cell In rng
If IsNumeric(cell.Value) And Not IsEmpty(cell.Value) Then
cell.Value = cell.Value + 10
End If
Next cell
End Sub
Common Mistakes with Range Variables
- Forgetting Set: rng = Range(“A1:A10”) without Set gives the “Object variable or With block variable not set” error (Error 91), because VBA tries to assign the values instead of the range.
- Declaring two variables in one line: in Dim rng1, rng2 As Range, only rng2 is a Range, and rng1 becomes a Variant. Write Dim rng1 As Range, rng2 As Range instead.
- Using the variable before Set: a range variable is Nothing until you set it, so using it before the Set line gives Error 91 as well.
Frequently Asked Questions
How do I check if a range variable has been set?
Use If rng Is Nothing Then. A range variable is Nothing until you assign a range to it with Set, or when a method like Find doesn’t find anything.
Do I need to activate the sheet to use a range variable?
No. Once you set the variable with the sheet name, like in Example-5, you can read and change its values from any sheet. You only need to activate the sheet if you want to select the range.
Can a range variable hold cells that are not next to each other?
Yes. You can set it to a range like Range(“A1:A5,C1:C5”), or join two ranges with Union(Range(“A1:A5”), Range(“C1:C5”)).
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.