VBA Range Variables: How to Declare and Use Them

A VBA Range variable is an object variable that refers to one or more cells in an Excel worksheet. Declare it with Dim rng As Range, then use Set to assign a specific range, such as Set rng = ThisWorkbook.Worksheets("Data").Range("A1:B5").

What a Range variable represents

In Excel VBA, Range is an object type for a cell or group of cells. A variable declared As Range stores a reference to such an object; it does not store the cell contents themselves. That distinction matters: the variable identifies where the cells are, while properties such as Value or Value2 let your code read or change what is in them.

A Range variable can refer to a single cell, a rectangular block, a whole row or column, or a multiple-area selection. For example, it could refer to A1, A1:C10, or an explicitly constructed union of separate areas. In ordinary macros, a single cell or rectangular block is usually easiest to understand and maintain.

Using a variable is useful when a macro will work with the same range more than once. Instead of repeating a long worksheet-and-range expression, assign it once and then use the shorter variable for reading, formatting, or other operations. It also makes code easier to change: if the target range changes, you can often update the assignment in one place.

How to declare and assign one

Declare the variable in a procedure or module with Dim and the Range type. Because a Range is an object, assigning it requires the Set statement. This is different from assigning a number or text value, which uses a normal assignment without Set.

Sub InspectRange()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cellCount As Long

    ' Point to the workbook that contains this macro.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set rng = ws.Range("A1:B5")

    cellCount = rng.Cells.Count
    Debug.Print rng.Address, cellCount
End Sub

This procedure assumes the workbook containing the macro has a worksheet named Data. The variable ws refers to that worksheet, and rng refers to cells A1 through B5 on it. The Cells.Count expression returns 10 for that two-column, five-row range. The result is printed in the VBA editor’s Immediate window, which you can show with Ctrl+G on Windows.

Before running a macro, save a copy of the workbook. VBA changes cannot be undone with Ctrl+Z, even when a macro changes only a small range. If a sheet name in the code does not exist, Excel raises an error rather than silently creating that sheet.

Why worksheet qualification matters

A Range reference belongs to a particular worksheet. Writing Range("A1") without identifying the worksheet can make code depend on which sheet is active. If the user activates another sheet before the macro runs, the macro may read or change the wrong cells. Assigning a worksheet variable first and then using ws.Range("A1") makes the target explicit.

ThisWorkbook means the workbook in which the VBA code is stored. ActiveWorkbook means the workbook currently active in Excel; those can be different, for example when code in one workbook operates on another. Use the one that matches the intended target, and qualify the worksheet as well. A worksheet name in a reference must match the tab name, including spaces and punctuation.

You can also build a reference from row and column numbers with the worksheet’s Cells property. For instance, ws.Cells(2, 1) refers to A2, because the row number comes first and the column number second. This is convenient when a row number comes from a loop or calculation rather than being fixed in the code.

Reading and changing cell contents

Once a Range variable points to the intended cells, use a property such as Value to get or set their contents. With rng already assigned to one cell, rng.Value = "Ready" writes text into that cell. To read a cell, assign its value to a variable of a suitable type, often a Variant when the contents might be text, a number, a date, or an error value.

For a multi-cell range, reading rng.Value or rng.Value2 generally returns a two-dimensional Variant array, with row and column positions represented in the array. Writing an array to a matching rectangular range can update many cells in one operation. The array dimensions need to match the target range; mismatches can cause errors or produce results different from those intended.

Value is a common choice for ordinary work. Value2 is also widely used when reading or writing raw cell values; unlike Value, it does not return VBA Currency or Date subtypes for those cell values. Excel still stores dates as serial numbers, so code using Value2 should interpret such numbers deliberately if it needs date handling. Neither property returns the displayed formatting: a cell can show a rounded number while its underlying value has more decimal places.

Using properties, methods, and related ranges

A Range variable gives access to more than cell values. Its properties include Address, Rows, Columns, Font, and Interior. Its methods include operations such as ClearContents and Copy. The action depends on the property or method you choose: changing Interior.Color affects fill color, while ClearContents removes cell contents but does not serve as a general command to remove all formatting.

For example, a macro can apply a fill color to a range variable after it has been assigned. The following sample changes cell formatting, so use it only on the intended sheet and save a workbook copy first:

Sub HighlightData()
    Dim ws As Worksheet
    Dim rng As Range

    ' Identify the worksheet and target cells explicitly.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set rng = ws.Range("B2:B10")

    rng.Interior.Color = RGB(255, 255, 0)
End Sub

Use Offset to refer to a range shifted by a number of rows or columns, and Resize to refer to a range with a different number of rows or columns. These return Range objects, so you can assign the result to another Range variable with Set. For example, a loop that moves down a list can use Set currentCell = ws.Cells(rowNumber, 1) on each pass.

Choosing a variable’s scope and lifetime

Most range variables belong inside the procedure that uses them. A declaration such as Dim rng As Range inside a Sub is local to that procedure, which limits accidental reuse and makes the code’s dependencies easier to see. When the procedure ends, its local variable goes out of scope; the worksheet cells it referred to remain in the workbook.

A module-level variable can be declared in the declarations section at the top of a module, outside any procedure, when several procedures genuinely need to share a reference. That longer lifetime also makes state harder to reason about: another procedure may change the reference, or a workbook or worksheet may not be the one expected later. Prefer passing a Range as a procedure argument or assigning it locally unless shared state is necessary.

For instance, a helper procedure can accept a range directly with a parameter declared ByVal target As Range. In this context, ByVal passes the object reference value to the procedure; it does not make the cells read-only. The helper can still change the referenced cells, so its purpose and side effects should be clear from its name and documentation.

Common errors and how to prevent them

The most frequent assignment error is omitting Set. The line rng = ws.Range("A1") attempts a value assignment rather than assigning the Range object and typically causes an object-related error. Use Set rng = ws.Range("A1"). Conversely, do not use Set when assigning a cell’s text or number to a normal variable.

Object variable not set: Code may try to use a Range variable before it has been assigned, or after it has been set to Nothing. Assign a valid range before accessing its properties.
Wrong worksheet: An unqualified Range or Cells reference may use the active sheet. Qualify both with the intended worksheet variable.
Subscript out of range: A worksheet lookup can fail when the sheet name is misspelled or the sheet is in a different workbook than the one referenced by ThisWorkbook.
Unexpected single-cell behavior: A single cell’s value is a scalar, whereas a multi-cell range’s value is typically a two-dimensional array. Code that assumes an array in both cases may fail.

To find these problems, step through the code with F8 in the Windows Visual Basic Editor and inspect the current values in the Locals or Immediate window. A breakpoint can pause execution on a line so you can check which workbook, worksheet, and address the variables refer to. On a Mac, Excel for Mac includes the Visual Basic Editor as well; the usual editor shortcut is Option+F11, and some keyboards require Fn as well. Excel for the web does not run VBA macros, so use desktop Excel for VBA code.

Range variables compared with addresses and selections

A string such as "A1:B5" is only text describing an address; it is not itself a Range object. A Range variable can be assigned by passing that address to a worksheet’s Range property. Keeping an address as text can be useful when it is assembled dynamically, but it must be converted to a real range before using Range properties or methods.

Likewise, Selection is not a substitute for a well-targeted Range variable. It represents what the user currently selected, which may change as the workbook is used and may not even be a cell range. A macro that always needs to operate on a known data area is generally more reliable when it refers directly to that area rather than relying on the user’s selection. Use selection-based code only when responding to an intentional user selection is part of the task.

For simple one-off tasks, you may not need a named Range variable at all: a fully qualified expression such as ws.Range("A1").Value = "Ready" can be clear. Use a variable when you reuse a range, pass it to another procedure, or need to build and inspect the reference in stages. The goal is readable, explicit code, not declaring a variable for every cell reference automatically.

Frequently Asked Questions

Do I need Set when assigning a Range variable?

Yes. A Range is an object, so use Set rng = ws.Range("A1"). Do not use Set to assign the cell’s value to a normal variable.

What is the difference between Range and Cells in VBA?

Both can identify cells. Range("A1") uses an address-style reference, while Cells(1, 1) uses row and column numbers. Qualify either with the worksheet you intend to use.

Can one Range variable refer to multiple cells?

Yes. It can refer to a contiguous block such as A1:C10, or to a multiple-area range created with an appropriate range expression. Some operations behave differently on multiple areas, so check the method’s requirements.

How do I clear cells through a Range variable?

After assigning the intended range, use rng.ClearContents to clear its contents while retaining formatting. Save a copy first because macro changes cannot be reversed with Ctrl+Z.

Can I use a Range variable in Excel for the web?

No. VBA macros do not run in Excel for the web. Open the workbook in desktop Excel for Microsoft 365 on Windows or Excel for Mac to use VBA.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.