Excel VBA Range Using Cells: Syntax, Examples, and Tips

In Excel VBA, use Cells(row, column) to identify a cell by its row and column numbers, and pass two Cells references to Range to define a rectangular block. For example, Range(Cells(2, 1), Cells(5, 3)) refers to A2:C5; qualify both references with a worksheet to make the code reliable.

What Range and Cells mean in VBA

Range and Cells are both ways to refer to cells in an Excel worksheet. Range is convenient when the address is known or easy to express as text, such as Range("A1") or Range("A1:C5"). Cells is useful when a row or column number comes from a variable, a loop, or a calculation.

A call such as Cells(2, 1) identifies the cell in row 2 and column 1: A2. The first argument is the row number and the second is the column number. Both numbering systems start at 1, so column 1 is A, column 2 is B, and column 27 is AA. The result is a Range object representing one cell, not a value by itself. Add a property such as .Value to read or change its contents.

It is therefore not quite a choice between two unrelated objects. Cells can supply a cell reference to Range, and Range can represent either one cell or many. The distinction is mainly about how you specify the reference: address-based with Range, or index-based with Cells.

Basic syntax and row-column indexing

The common pattern for a rectangular range is Range(startCell, endCell). The start and end can be cell references created with Cells. Excel includes both endpoint cells, so the two corners define the full rectangle between them.

Sub SelectBlockByNumbers()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Data")
    ws.Range(ws.Cells(2, 1), ws.Cells(5, 3)).Select
End Sub

In this example, the two endpoints are A2 and C5, so the selected block is A2:C5. Selection is shown to make the result visible, but most macros do not need to select cells; they can work with the range directly. Also, a macro that selects a range may fail if its worksheet is not active. Directly assigning or reading a range is generally more dependable.

For a fixed address, Range("A2:C5") is shorter and often clearer. Use the numeric form when the address must change—for example, when processing a row number stored in a variable or building a range based on the last row of a list.

Combining Cells with Range in practical examples

Use a worksheet variable and set its reference before referring to cells. This example writes text to C2 by using row 2 and column 3:

Sub WriteToCellByIndex()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Data")
    ws.Cells(2, 3).Value = "Ready"
End Sub

For a multi-cell range, give Range the top-left and bottom-right cells. In the next example, the range is B2:D5. Assigning one value to a multi-cell range places that value in each cell in the range.

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

    Set ws = ThisWorkbook.Worksheets("Data")
    Set block = ws.Range(ws.Cells(2, 2), ws.Cells(5, 4))
    block.Value = "Reviewed"
End Sub

This pattern is especially handy when the row boundaries are calculated at run time. For example, replace the numbers 2 and 5 with variables to work on a block whose height depends on the size of the data. Keep the column numbers explicit or store them in named variables if that makes the code easier to understand.

Why worksheet qualification matters

Unqualified references such as Cells(2, 1) and Range("A1") refer to the active sheet in many common VBA contexts. The active sheet can change when a user clicks another tab, when another macro activates a sheet, or when code opens a workbook. A macro may then read or edit the wrong sheet, or produce an error if it tries to select a cell on an inactive sheet.

Anchor both the Range and each Cells reference to the same worksheet. A With block can reduce repetition, but each reference inside it needs a leading period:

Sub FormatDataBlock()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Data")
    With ws
        .Range(.Cells(2, 1), .Cells(10, 3)).Interior.Color = vbYellow
    End With
End Sub

The leading periods make the references belong to ws. Without them, a Cells reference inside the block could still point to the active sheet rather than the worksheet named in With. ThisWorkbook means the workbook containing the VBA code. If the macro is intended to operate on the workbook currently selected by the user, that may instead be ActiveWorkbook; choose deliberately rather than substituting one without considering which file should be changed.

Reading and changing values

To get the contents of a cell, use its .Value property. To write a value, assign to the same property. A single-cell reference is straightforward:

Sub CopyOneValue()
    Dim ws As Worksheet
    Dim cellValue As Variant

    Set ws = ThisWorkbook.Worksheets("Data")
    cellValue = ws.Cells(2, 3).Value
    ws.Cells(3, 3).Value = cellValue
End Sub

The code reads C2 and writes the value to C3. The variable is declared as Variant because a cell might contain text, a number, a date, an error value, or be empty. For values whose type is known and controlled, a more specific variable type can make the intent clearer, but it should match the data being handled.

A range can also be assigned to a variable with Set, as in Set block = ws.Range(...). This stores a reference to the cells, not a copy of their contents. Use Set when assigning an object such as a Range to an object variable; ordinary values such as text or numbers are assigned without Set.

Choosing between Range, Cells, and direct addresses

Use the form that makes the reference easiest to read and maintain. A fixed address is often clearest as Range("B2:D20"). A row or column that changes during a loop is usually easier to express with Cells. Combining them lets you define a changing rectangle without assembling address strings.

Use Range("A1") or Range("A1:C5") when the address is fixed and the letter-based form is easy to understand.
Use Cells(rowNumber, columnNumber) when either index is calculated, such as in a loop through records.
Use Range(Cells(firstRow, firstColumn), Cells(lastRow, lastColumn)) when both corners are determined at run time.
Use a worksheet variable when the code should consistently act on a particular sheet rather than whichever sheet happens to be active.

For instance, a macro that processes a status column row by row can refer to each status cell as ws.Cells(rowNumber, 2). That is more flexible than constructing text such as "B" & rowNumber, particularly when the column number is also variable. On the other hand, the literal address Range("B2") can be easier for a reader to recognize than Cells(2, 2). Readability is a practical reason to choose one form over another.

Common errors and ways to prevent them

A frequent source of bugs is qualifying only part of a reference. Writing ws.Range(Cells(2, 1), Cells(5, 3)) qualifies the outer range but leaves both endpoint references unqualified. If a different worksheet is active, those endpoints can refer to the wrong sheet. Use ws.Range(ws.Cells(2, 1), ws.Cells(5, 3)), or the leading-period form inside With ws.

Another common mistake is reversing the Cells arguments. Cells(3, 2) means row 3, column 2 (B3), not column 3, row 2. If a number represents a column, put it in the second position. Naming variables rowNumber and columnNumber can prevent confusion when the code becomes more involved.

Check that the worksheet name exists exactly as written and that the intended workbook is referenced. If code is run from a different workbook, ThisWorkbook may not be the file containing the data. Also avoid assuming that selecting is necessary: direct references such as ws.Cells(2, 3).Value = "Ready" do not depend on making the target sheet active.

Working with larger or changing ranges safely

When the data size varies, compute the boundaries first, then use those numbers with Cells. The right method for finding the last row depends on the layout: a chosen column should be populated reliably, and blank rows within the data can affect assumptions. Do not copy a last-row expression without checking that its column and blank-cell behavior fit the workbook.

For repeated tasks, keep worksheet, row, and column references in variables with descriptive names. This makes it easier to inspect which cells the macro will change before running it. When possible, read or write a whole block through one Range reference rather than repeatedly selecting individual cells; the code is often simpler and avoids unnecessary screen interaction. The best approach still depends on whether the task needs to inspect each cell separately or can handle the block as a whole.

Before running any macro that changes workbook contents, save a separate copy of the workbook. VBA changes cannot be undone with Ctrl+Z, so a backup is the reliable way to recover from an unintended edit. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you need to retain its macros. Only enable macros in files you trust.

Frequently Asked Questions

Can I use Cells inside Range in Excel VBA?
Yes. For example, Range(Cells(2, 1), Cells(5, 3)) defines A2:C5 when the references point to the intended worksheet. In robust code, qualify all three references with the same worksheet object.

Does Cells(1, 1) mean A1?
Yes. The first argument is the row and the second is the column, and both start at 1. Therefore, row 1, column 1 is A1; row 2, column 3 is C2.

Why does Range(Cells(…), Cells(…)) sometimes refer to the wrong sheet?
Because unqualified Cells references can use the active sheet. Qualify the endpoints, for example ws.Range(ws.Cells(2, 1), ws.Cells(5, 3)), so the whole reference is tied to ws.

Should I use Range or Cells in a VBA loop?
Use Cells when a row or column index changes in the loop, since it accepts numeric indexes directly. Use Range for a fixed address or when its address-based form makes the code easier to read.

Do I need to select a cell before changing it?
No. A qualified reference can be read or changed directly, such as ws.Cells(2, 3).Value = "Ready". Avoiding selection usually makes a macro less dependent on which worksheet is active.

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.