Excel VBA Range Syntax: Examples and Best Practices

In Excel VBA, a range is a cell or group of cells, usually referenced with Range("A1") or Range("A1:C5"). For dependable code, qualify the range with its worksheet, such as Worksheets("Data").Range("A1:C5"), so VBA does not use whichever sheet happens to be active.

What a Range Reference Means

A VBA Range object represents one cell, a rectangular block, an entire row or column, or a collection of cell areas. The reference is written in Excel’s familiar A1 notation, but VBA uses it as an object that can be read, changed, formatted, or passed to another method. The syntax is not an Excel worksheet formula: it belongs in VBA code, such as a procedure in a standard module or a worksheet module.

The simplest forms are:

Range("B4") refers to one cell.
Range("B4:E12") refers to a rectangular block, including both corner cells.
Range("B:B") refers to the whole B column.
Range("4:4") refers to the whole fourth row.

A range can also represent multiple areas, but separate areas are usually easier to combine with the Union method. A range is not limited to cells containing data; a reference such as Range("A1:C5") remains valid even when some or all of those cells are blank.

Basic Range Syntax and Worksheet Qualification

When VBA sees Range("A1") without a worksheet qualifier, it treats it as a reference on the active worksheet. That makes short examples easy to write, but it creates a common bug: the macro can read from or change the wrong sheet if a user activates another tab before running it. Qualifying the reference makes the target explicit.

Before running any macro that changes workbook data, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, so a backup is the simplest way to recover from an unintended change.

Sub WriteOneCell()
    Dim ws As Worksheet
    Dim target As Range

    ' Use the named sheet rather than the active sheet.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set target = ws.Range("B2")
    target.Value2 = "Reviewed"
End Sub

Here, ThisWorkbook means the workbook containing the VBA code. By contrast, ActiveWorkbook means the workbook currently in the foreground, which may be a different file. Use ActiveWorkbook only when that distinction is intentional. A worksheet variable is helpful when several references use the same sheet; it reduces repetition and makes the target easier to inspect.

In Microsoft 365 for Windows, open the VBA editor with Alt+F11. On Mac, use Option+F11 (on some keyboards, Fn may also be needed), or open the Visual Basic Editor from the Developer tab. The editor and macro behavior differ somewhat by platform, but the core Range and Cells syntax is the same. Excel for the web can open a workbook containing VBA, but it cannot create, edit, or run VBA macros; use the desktop application.

Using Cells for Row and Column Numbers

Cells(row, column) is useful when a row or column number comes from a variable, a loop, or a calculation. The row and column arguments are numbers, and the first cell is row 1, column 1. For example, Cells(2, 3) means C2. It is often clearer to qualify Cells with the same worksheet as the rest of the reference.

Sub MarkStatus()
    Dim ws As Worksheet
    Dim rowNumber As Long

    ' Save a copy before running a macro that changes cells.
    Set ws = ThisWorkbook.Worksheets("Data")
    rowNumber = 5
    ws.Cells(rowNumber, 3).Value2 = "Complete"
End Sub

This writes the text to C5. A frequent source of confusion is mixing an unqualified Range with a qualified Cells: the pieces may then refer to different worksheets. To build a block from numeric coordinates, put both Cells references inside the worksheet’s Range method.

Sub FormatBlock()
    Dim ws As Worksheet
    Dim block As Range

    ' Save a copy before running a macro that changes formatting.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set block = ws.Range(ws.Cells(2, 2), ws.Cells(8, 4))
    block.Font.Bold = True
End Sub

The constructed block runs from B2 through D8. This pattern is particularly useful when the start and end row are stored in variables. In a With ws block, shorthand references such as .Cells(2, 2) and .Range(.Cells(2, 2), .Cells(8, 4)) are also valid; the leading periods ensure those references belong to the worksheet named by the With statement.

Choosing Between Range and Cells

Use Range when the address is known and readable in A1 notation, and use Cells when a coordinate is calculated. They work together: Range(Cells(...), Cells(...)) creates a rectangle whose boundaries can change while the code runs. Neither style is universally better; the practical choice depends on whether the location is fixed or dynamic.

Fixed label or setting: ws.Range("A1").
Looping down a column: ws.Cells(rowNumber, 1).
Variable-sized rectangle: ws.Range(ws.Cells(firstRow, firstColumn), ws.Cells(lastRow, lastColumn)).
Entire column: ws.Columns(2) or ws.Range("B:B").

Using numeric column indexes can be convenient in loops, but the meaning of values such as 2 or 7 may not be obvious later. A descriptive constant or a comment can help. Conversely, a long A1 address built by string concatenation can be harder to maintain than a Cells expression, especially when row numbers change.

Dynamic Ranges with Offset and Resize

Offset moves a reference by a number of rows and columns without changing its size. Resize changes the number of rows or columns covered. Both are useful for relative references, but the resulting range still needs a worksheet-qualified starting point.

For example, if ws.Range("B2") is the starting cell, ws.Range("B2").Offset(1, 0) refers to B3. ws.Range("B2").Resize(3, 2) refers to B2:C4: three rows high and two columns wide. The first argument to Resize is the row count, and the second is the column count. A negative offset can move upward or left, so make sure the resulting reference stays within the worksheet.

A common task is finding the last used row in a particular column. This pattern starts at the bottom of that worksheet’s column A and moves upward to the first nonblank cell it encounters:

Sub FindLastRowInColumnA()
    Dim ws As Worksheet
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    MsgBox lastRow
End Sub

If column A contains data through row 27 with no later entries, lastRow is 27. This method finds the last nonblank cell in that column, not necessarily the last row used anywhere on the sheet. If the column is empty, the result is 1, so code that assumes there is a header or data should check for that case. Blanks within a column do not stop the search from finding a later nonblank cell.

Reading, Writing, and Formatting Ranges

Once a range is set, use properties such as Value2 to read or write cell contents, and properties such as Font to format them. For a single cell, target.Value2 = "Reviewed" is explicit and readable. Value2 returns cell values without the Currency and Date data-type conversions associated with the older Value property; Excel’s displayed number format is not itself changed by assigning a value.

For a multi-cell range, reading Value2 generally returns a two-dimensional Variant array, with row and column positions corresponding to the cells. This can be more efficient than reading cells one by one in a large loop. When assigning a two-dimensional array, its dimensions need to match the target range’s row and column dimensions. A one-cell range behaves differently from a multi-cell range when read into a variable, so code that handles both cases may need to account for that distinction.

Formatting uses the same range reference. For example, block.Font.Bold = True applies bold formatting to the cells in block. A range reference identifies the cells; the property or method after it determines what VBA does. This separation makes it possible to reuse a range variable for reading values, applying formatting, or passing the cells to another procedure.

Common Range Syntax Problems

Many range errors come from ambiguity rather than incorrect A1 notation. The following checks resolve a large share of them:

Wrong active sheet: qualify Range and Cells with a worksheet variable rather than relying on the selected tab.
Object variable not set: use Set when assigning an object, as in Set block = ws.Range("A1:B2"). A Range variable is an object.
Unqualified Cells: inside a worksheet-specific range, qualify both endpoint references with that same worksheet.
Invalid address string: check quotation marks, colons, and any row or column numbers assembled from variables.
Unexpected last row: confirm that the column used for the search reliably contains the records you want to count.

Another misconception is that selecting a range is required before changing it. It is not. Directly writing ws.Range("A1").Value2 = "Ready" avoids changing the user’s selection and is usually more dependable than selecting a cell and then acting on Selection. Selection is useful when a macro intentionally guides a user through the sheet, but it is not a prerequisite for ordinary range operations.

Frequently Asked Questions

What is the difference between Range and Cells in VBA?
Range commonly uses A1-style addresses, such as Range("C5"). Cells uses numeric row and column indexes, such as Cells(5, 3) for C5. Both refer to worksheet cells, and both should be qualified with the intended worksheet in reliable code.

How do I refer to a range on another worksheet?
Use the worksheet’s Range property, for example ThisWorkbook.Worksheets("Data").Range("A1:C10"). If the workbook is not the one containing the macro, qualify the worksheet through the appropriate open Workbook object as well.

How do I select an entire column or row?
Use ws.Columns("B:B") or ws.Range("B:B") for column B, and ws.Rows("4:4") or ws.Range("4:4") for row 4. Whole-column and whole-row references include blank cells as well as populated ones.

Why does Range(“A1”) sometimes affect the wrong sheet?
Without a worksheet qualifier, Range("A1") refers to the active sheet in the relevant context. If another sheet is active when the macro runs, the unqualified reference can point there. Assign a worksheet to a variable and use ws.Range("A1") to make the target explicit.

Can I undo a VBA macro with Ctrl+Z?
Usually not. Running VBA clears Excel’s normal undo history, and macro changes cannot generally be reversed with Ctrl+Z. Save a separate copy before running code that edits or formats a workbook.

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.