VBA Range Set: How to Set and Use Excel Cell Ranges

In Excel VBA, use the Set keyword to assign a cell or range reference to an object variable: for example, Set rng = Worksheets("Data").Range("A1:B5"). Declare rng as a Range first; use rng.Value when you want to read or change the cells’ contents rather than assign the range object itself.

What “Set Range” Means in VBA

A VBA Range represents one or more cells in a worksheet. A variable declared as Range can hold a reference to that object, which lets code use the same cells repeatedly without writing the full address each time. The keyword Set is VBA’s object-assignment syntax: it assigns an object reference to an object variable.

This is different from assigning a value. For example, Set rng = ws.Range("A1") makes rng refer to cell A1. By contrast, rng.Value = "Ready" writes the text Ready into the cell or cells represented by rng. You do not use Set to assign ordinary values such as text, numbers, or dates.

A typical declaration and assignment look like this:

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

    ' Point to a specific worksheet and its cell range.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set rng = ws.Range("A1:B5")

    ' Write a value to every cell in the range.
    rng.Value = "Review"
End Sub

Before running any macro that changes a workbook, save a copy. VBA changes generally cannot be reversed with Ctrl+Z.

Why Worksheet Qualification Matters

The expression Range("A1:B5") does not identify a worksheet by itself. If used without a worksheet qualifier in ordinary worksheet code, it refers to the active sheet. That can make a macro behave differently depending on which sheet happens to be selected when the code runs.

For predictable results, qualify the range through a worksheet variable, as in ws.Range("A1:B5"), and set that variable explicitly. ThisWorkbook means the workbook containing the VBA code. ActiveWorkbook means the workbook currently active in Excel, which may be a different file. Choose deliberately: if the macro is stored in an add-in or personal macro workbook, ThisWorkbook may not be the data file you intend to edit.

For example, this version always targets the sheet named Data in the workbook containing the macro:

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

    ' Save a separate copy before running a destructive macro.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set rng = ws.Range("A2:C100")

    rng.ClearContents
End Sub

The sheet name must match an existing worksheet tab. If it does not, VBA raises a subscript-out-of-range error when it tries to retrieve the worksheet. A worksheet’s tab name is not necessarily the same as its VBA code name, so use the tab name with Worksheets("...").

Set a Range with A1 Addresses or Cells

The simplest way to define a fixed range is with an A1-style address, such as ws.Range("D2:F20"). You can also refer to a single cell with ws.Range("D2"), or use the Cells property when row and column numbers are easier to calculate. In VBA, Cells(2, 4) means row 2, column 4, or D2.

Using row and column numbers is useful when the range depends on a loop or a variable. For example, ws.Cells(2, 1) identifies A2. A range can be constructed from two cell references, as in ws.Range(ws.Cells(2, 1), ws.Cells(10, 3)), which covers A2:C10. Keep both endpoint cells qualified with the same worksheet to avoid ambiguity.

Here is an example that writes a heading to A1 and a value to B1:

Sub WriteHeader()
    Dim ws As Worksheet
    Dim headerCell As Range

    Set ws = ThisWorkbook.Worksheets("Data")
    Set headerCell = ws.Cells(1, 1)

    headerCell.Value = "Status"
    ws.Cells(1, 2).Value = "Open"
End Sub

Notice that only headerCell needs Set, because it is an object variable. The assignments to .Value set cell contents and therefore do not use Set.

Choose a Fixed or Dynamic Range

A fixed address is clear and appropriate when the data area is a known size. If rows are added or removed, however, a hard-coded endpoint such as row 100 may omit new data or include empty cells. A dynamic range calculates its boundary when the macro runs.

One common approach is to find the last non-empty cell in a column with End(xlUp). This example treats column A as the key column and includes the header in row 1:

Sub SetRangeToLastRow()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim dataRange As Range

    Set ws = ThisWorkbook.Worksheets("Data")

    ' Find the last non-empty cell in column A.
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    ' Include columns A through C, starting at the header row.
    Set dataRange = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, 3))

    dataRange.AutoFilter
End Sub

This technique depends on column A being populated for every data record. If there are no data rows below the header, lastRow will be 1. If the key column contains gaps, the last used row can still be found, but the range will include those gaps. Choose a column that reliably marks the records, or add checks appropriate to the workbook.

Other options include a named range or a structured table. A table’s DataBodyRange refers to its data rows, while ListObject properties can provide access to the header and table as a whole. Tables are often a good fit for lists that expand over time because Excel maintains their boundaries as rows are added. An empty table may have no data body, so code that uses DataBodyRange should account for that possibility.

Use the Range After Setting It

Once a variable refers to a range, you can use its properties and methods. Value reads or writes cell values; ClearContents removes contents without removing formatting; Interior.Color changes fill color; and Address returns the address. For a multi-cell range, assigning one value to Value puts that value in every cell. Reading a multi-cell range into a Variant typically returns a two-dimensional array.

For example, a one-cell range can be read into a simple variable. For multiple cells, use a Variant if you want to capture the returned array:

Sub ReadRangeValues()
    Dim ws As Worksheet
    Dim values As Variant

    Set ws = ThisWorkbook.Worksheets("Data")

    ' Read the values from A1:C10 into an array.
    values = ws.Range("A1:C10").Value

    ' The array can now be inspected or processed in VBA.
    Debug.Print values(1, 1)
End Sub

Use the variable when it makes a procedure clearer or when several operations target the same cells. For a single simple operation, a qualified expression such as ws.Range("A1").Value = "Done" may be easier to read than declaring an additional range variable.

Common Errors and How to Avoid Them

“Object variable or With block variable not set.” The range variable was declared but never assigned, or its reference was cleared. Run the Set statement before using the variable, and check any conditional path that might skip it.
“Expected: expression” or a syntax error. Check that the object assignment begins with Set, that the variable is declared as Range, and that parentheses and quotation marks are balanced.
The wrong sheet changes. An unqualified Range or Cells reference may act on the active sheet. Qualify both through the intended worksheet, for example ws.Range(ws.Cells(1, 1), ws.Cells(5, 3)).
The range address is invalid. Confirm that a worksheet name exists and that the address is valid. For a worksheet with spaces in its name, the VBA form Worksheets("Monthly Data") is valid; worksheet names passed as text must match exactly.
Code uses Set on a value. Use Set for object references such as Range and Worksheet. Use ordinary assignment for properties such as rng.Value or ws.Name.

It is also worth distinguishing Set rng = Nothing from clearing worksheet cells. The first releases the variable’s object reference; it does not delete or blank the cells. To remove cell contents, use a method such as rng.ClearContents.

Run VBA in Excel for Windows, Mac, and Web

These examples use Excel desktop for Microsoft 365 on Windows. To open the Visual Basic Editor on Windows, press Alt+F11. In Excel for Mac, the editor and VBA support are available in the desktop app; the keyboard shortcut may depend on the keyboard’s function-key settings, and the editor can also be opened from Tools > Macro > Visual Basic Editor. The Mac interface and some VBA features differ from Windows, so check platform-specific compatibility when a macro uses operating-system calls, controls, or external references.

Excel for the web does not run VBA macros or provide the desktop Visual Basic Editor. A workbook containing VBA can be opened there, but use Excel desktop to edit or run its VBA code. Saving a macro-enabled workbook normally uses the .xlsm format; saving it as a macro-free format can remove the VBA project. If Excel displays a security warning, only enable macros in files you trust.

Frequently Asked Questions

How do I set a range in VBA?
Declare a variable as Range, then assign a qualified worksheet range with Set, such as Set rng = ThisWorkbook.Worksheets("Data").Range("A1:B5").

Do I need Set for a single cell?
Yes, if you assign that cell to an object variable such as cell As Range. You do not need a variable at all for a direct action such as ws.Range("A1").Value = 10.

What is the difference between Range and Cells?
Range commonly uses A1-style addresses, while Cells(row, column) identifies a cell with numeric row and column indexes. Both refer to cells and can be qualified with a worksheet.

How do I set a range to the last used row?
Use a dependable key column and find its last non-empty cell with ws.Cells(ws.Rows.Count, columnNumber).End(xlUp).Row. Then build the range from the starting cell to that row, while checking whether the sheet contains data.

Can I undo a VBA range change with Ctrl+Z?
Usually not. Running a macro clears Excel’s normal undo history, so save a separate copy before running code that changes cells, formatting, or workbook structure.

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.