VBA Range Index: How Cells, Item, and Offsets Work

In VBA, a range index identifies a cell by its row and column position, starting at 1. For example, Range("B2:D5").Cells(2, 3) refers to D3: the second row and third column within that range.

What a VBA range index means

A range index is a position, not a worksheet address written as text. The first index identifies a row and the second identifies a column. Both are one-based, so the top-left cell is position (1, 1), not (0, 0). This is similar to reading a small grid: count down for the row and across for the column.

The important detail is that the starting point depends on which object you index. If you index a worksheet’s Cells property, positions are measured from the worksheet’s top-left cell, A1. If you index Cells on a range, positions are measured from that range’s top-left cell. The same pair of numbers can therefore identify different cells in those two contexts.

For example, the second row and third column of the worksheet is C2. But the second row and third column of B2:D5 is D3. Keeping track of the object before the index is the key to understanding VBA range references.

Using Cells and Item

The most readable way to select a cell by position inside a range is to use its Cells property with two arguments: Cells(row, column). The Item property can also take row and column positions. For a rectangular range, these forms identify the same cell:

Range("B2:D5").Cells(2, 3)
Range("B2:D5").Item(2, 3)

In either case, the range’s upper-left cell, B2, is position (1, 1). Moving to row 2 and column 3 lands on D3. You can use the returned Range object to read or change the cell’s value, formatting, or other properties. For instance, adding .Value reads or writes the cell contents.

Cells is often preferred because it makes the row-and-column interpretation clear, especially when the numbers come from variables. Item is useful to know because Excel’s Range object supports it directly, but the two-argument form avoids ambiguity and is easy to maintain.

Worksheet indexes versus range indexes

Compare a worksheet-level reference with a range-relative one. With ws set to a worksheet, ws.Cells(2, 3) refers to C2 on that sheet. In contrast, ws.Range("B2:D5").Cells(2, 3) refers to D3. The first object is the entire worksheet grid; the second is a smaller grid whose origin is B2.

Qualifying references with a worksheet variable is safer than relying on whichever sheet happens to be active. For example, if the active sheet changes while a macro runs, an unqualified Cells(2, 3) may refer to a different worksheet than intended. A qualified expression such as ws.Cells(2, 3) makes the target explicit.

This distinction also explains a common misconception: the row and column arguments after a range do not necessarily mean worksheet row and column numbers. They are relative positions inside the range object that precedes them. To refer to worksheet row 2, column 3 regardless of a smaller range, use the worksheet’s Cells property.

A practical range-index example

This macro writes a value into the second row and third column of B2:D5. That position is D3. The example uses a worksheet variable so the destination does not depend on the active sheet.

Option Explicit

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

    ' Set the worksheet that contains the destination range.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Row 2, column 3 relative to B2:D5 is cell D3.
    Set target = ws.Range("B2:D5").Cells(2, 3)
    target.Value = "Complete"
End Sub

Before running a macro, save a copy of the workbook. Macro changes generally cannot be reversed with Ctrl+Z, so the undo history is not a substitute for a backup. The sheet name in the example must also match a worksheet tab in the workbook; otherwise VBA will raise an error when it tries to set ws.

When a single index is less clear

You may encounter references such as someRange.Cells(5), which use one index rather than a row-and-column pair. Excel allows single-index references in some Range contexts, but they can be harder to interpret: the reader must understand how the cells are being enumerated and how the range object is defined. They are especially easy to misread when the range is not a single row or column.

For ordinary cell selection, prefer Cells(row, column) and state both positions. This makes the intended grid location explicit and avoids a reader having to work out what a lone number means. If a single index is required by a particular routine, document the order and test the behavior against the exact range being used rather than assuming it means a worksheet row number.

Do not confuse a range index with a cell’s address. Cells(2, 3) is a position relative to its parent object, while Range("C2") names a worksheet address when properly qualified. A position is often more useful in loops; an address is often easier to read for a fixed cell.

Indexes can point outside the stated range

A subtle behavior is that an index larger than the range’s dimensions does not always trigger an immediate “out of range” error. Excel can calculate the indexed cell relative to the range’s top-left corner, even if that cell lies beyond the range’s visible boundary. For example, the row 6, column 1 position relative to B2:D5 resolves to B7, outside B2:D5.

That behavior can make a macro appear to work while writing to the wrong place. If row and column values are calculated from worksheet data, check that they fall within the intended range dimensions before using them. For B2:D5, valid relative row positions are 1 through 4 and valid relative column positions are 1 through 3.

You can build checks into code rather than silently accepting unexpected positions. For a range stored in a variable named dataRange, its dimensions are available through dataRange.Rows.Count and dataRange.Columns.Count. Compare the requested indexes with those counts before accessing the cell, and decide explicitly whether an invalid position should be skipped or reported.

Range indexes and VBA arrays

Another related source of confusion is assigning a multi-cell range’s values to a VBA variable. When a contiguous multi-cell range is read through .Value, VBA returns a two-dimensional Variant array. Its first subscript represents the row and its second represents the column, so the pattern is also values(row, column). That similarity does not make the array a Range object: it contains values, not cell references.

For example, if a variable receives the values of B2:D5, then the array position (2, 3) corresponds to the value that came from D3. Changing that array element changes the in-memory data; it does not automatically update the worksheet. To send edited array contents back, assign the array to a suitably sized range. Array bounds can also depend on how an array was created, so check its bounds when the data did not come directly from a range.

Use a Range index when you need to work with an actual worksheet cell or its properties. Use an array index when you have copied range values into memory for processing. Mixing up these two objects can lead to code that updates a temporary value but leaves the worksheet unchanged.

Common mistakes and safer habits

Starting at zero: Excel range positions are one-based. Use 1 for the first row or column.
Reversing the arguments: The order is row, then column—not column, then row.
Forgetting the origin: A range’s indexes start at its top-left cell, while worksheet indexes start at A1.
Leaving a reference unqualified: Use a worksheet variable or an explicit worksheet reference so the active sheet cannot redirect the operation.
Assuming the range enforces a boundary: Validate calculated positions against the range’s row and column counts.
Using Select unnecessarily: Assign a value through the Range object directly when possible; selecting cells makes code more dependent on the active sheet and selection.

For repeated work, set the source range once, use named row and column variables, and make the coordinate system apparent in comments. For example, name variables relativeRow and relativeColumn when they refer to positions within a range. Those names help distinguish them from worksheet row numbers.

Frequently Asked Questions

Is VBA range indexing zero-based or one-based?
It is one-based. The first cell in a range is Cells(1, 1). VBA arrays can have different lower bounds depending on how they are declared or created, so do not assume every array follows the same indexing rules as Excel ranges.

What does Range(“B2:D5”).Cells(2, 3) refer to?
It refers to D3. The index is relative to B2, the upper-left cell: move down one row to row 2 of the range, then across to its third column.

What is the difference between Cells(2, 3) and Range(“C2”)?
Cells(2, 3) identifies row 2, column 3 relative to its parent worksheet or range. A qualified Range("C2") names the fixed address C2 on that worksheet.

Can a range index refer to a cell outside the range?
Yes. Excel may resolve an index relative to the range’s top-left cell even when the resulting cell is beyond the range boundary. Check the requested row and column against the range dimensions if staying inside the range matters.

Should I use Cells or Item?
Both support row-and-column indexing on a Range. Cells(row, column) is usually the clearest choice for ordinary VBA code because the coordinate order is familiar and explicit.

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.