VBA Cells vs Range in Excel — Reference Cells by Number (and Why Cells(1, 2) Is B1, Not A2)

TL;DR — Cells is Range addressed by number. Range("A1") names a cell with a string in the order you read it (column letter, then row). Cells(row, column) names one cell with two integers in the order Excel stores it — row first, then column — which is why Cells(1, 2) is B1, not A2. Because both coordinates are numbers you can compute, Cells is the reference built for loops. Use Range for regions you type by name, Cells for positions you calculate, and Range(Cells(1, 1), Cells(n, k)) to build a block from computed corners.

Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Data")
' Range names a fixed region; Cells computes a position row-first.
ws.Range("A1").Value = "Header"          ' the human way - a typed address
ws.Cells(2, 1).Value = "First row"       ' row 2, column 1 = A2 (NOT B2)

Referring to cells is the first thing every macro does, and Cells versus Range is the first place people trip. The confusion is not that one is better — it is that they address cells differently, and mixing up the order silently writes to the wrong place. This guide is built on one idea that makes the rest predictable: Cells is the numeric twin of Range, and its arguments run row-first. Once you hold that, the loop patterns and the off-by-orientation bugs both make sense.

What you'll learn

• The mental model — Cells is Range addressed by two numbers instead of a string
• The rule that matters most — Cells(row, column), so Cells(1, 2) is B1, not A2
• Building a block from computed corners with Range(Cells(...), Cells(...))
• Why a bare Cells with no index means every cell on the sheet
• Indexing into an existing range with rng.Cells(i) and its row-major order
• When to reach for Range and when to reach for Cells — and why not to loop it

The mental model: Cells is Range addressed by number

A cell reference has to answer one question — which cell? — and VBA gives you two ways to answer it. Range("A1") answers with a string in spreadsheet notation: the column letter first, then the row number, exactly as your eye scans the grid. Cells(1, 1) answers with two integers: the row, then the column.

ws.Range("B3").Value = 10    ' string address: column B, row 3
ws.Cells(3, 2).Value = 10    ' same cell, as numbers: row 3, column 2

They point at the same cell. The difference that matters is what you can do with the address: a string like "B3" is fixed text, but Cells(3, 2) is two numbers you can calculate. That is the entire reason Cells exists — it is the reference you can drive from a counter, a UBound, or a row you found at runtime. Range is for the region you know by name; Cells is for the position you work out in code.

The rule that matters most: Cells is (row, column), so Cells(1, 2) is B1

Here is the line that catches everyone coming from typing "A2". The arguments are row first, column second — down before across — which is the opposite of the "letter then number" order you read on the sheet:

ws.Cells(1, 2).Value = "here"   ' row 1, column 2  =>  B1   (NOT A2)
ws.Cells(2, 1).Value = "there"  ' row 2, column 1  =>  A2

If you expected Cells(1, 2) to be A2 because "A is column 1 and 2 is the row," you read it in grid order; VBA reads it in storage order. A reliable mnemonic: R before C — Row, then Column, the same order as R1C1 notation and matrix subscripts. Get this backwards inside a loop and every write lands one axis off, transposed across the sheet, with no error to warn you. When in doubt, say it out loud as "row r, column c" every time.

The column argument will also accept a letter as a string, which is handy when you know the column but compute the row:

ws.Cells(lastRow + 1, "C").Value = total   ' row is computed, column C is known

Building a block from computed corners: Range(Cells, Cells)

Cells addresses a single cell, so on its own it cannot describe a block. The idiom that unlocks dynamic ranges is passing two Cells calls as the corners of a Range:

Dim lastRow As Long, lastCol As Long
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row       ' bottom of column A
lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
' Build the whole data block from two computed corners:
Dim data As Range
Set data = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))   ' A1 : last cell

This is the payoff of learning Cells: Range("A1") alone cannot take a computed bottom-right corner without gluing a string together ("A" & lastRow), which breaks the moment your columns run past Z. Range(Cells(1, 1), Cells(lastRow, lastCol)) stays numeric and never cares how wide the data is. From here you can reshape the reference with Resize or let Excel find the block for you with CurrentRegion.

A bare Cells means the whole sheet

Cells with no index is not an error and not a single cell — it is the entire worksheet's cell collection. That is genuinely useful for sheet-wide operations:

ws.Cells.ClearContents          ' wipe every value on the sheet
ws.Cells.Font.Name = "Calibri"  ' set the font everywhere at once

But it is also a trap: a bare Cells where you meant one specific cell quietly acts on all 1,048,576 × 16,384 of them. If a "format one cell" macro reformats the whole sheet, look for a Cells that lost its index. The safe habit is to always give Cells its (row, column) unless you truly mean everything.

Cells inside a range, and the linear index

Cells is not only a worksheet property — it also works relative to a range, where (1, 1) is that range's own top-left corner rather than A1:

Dim blk As Range: Set blk = ws.Range("C5:F20")
blk.Cells(1, 1).Value = "top-left of the block"   ' C5, not A1

There is also a single-index form, Cells(i), that walks the cells in row-major order — left to right across a row, then down to the next:

blk.Cells(1).Value   ' C5   (first cell)
blk.Cells(5).Value   ' C6   (four across the 4-wide block, then wraps to the next row)

The surprise here is the direction: Cells(i) counts across before down, so in a 4-column block cell number 5 is the start of the second row, not the fifth row. If a linear walk seems to jump to the wrong place, it is almost always this row-major order versus a column-major expectation.

Range for regions, Cells for positions — and don't loop it

The clean rule is not "prefer one." It is: use Range for regions you name and Cells for positions you compute. A fixed report area, a named block, a header row you type once — that is Range("A1:D1"). A cell whose row or column you calculate at runtime — that is Cells(r, c). They compose: Range(Cells(...), Cells(...)) builds a named-shaped block out of computed corners.

The one thing to resist is reaching for Cells inside a cell-by-cell loop when a whole-range operation exists. Writing For r = 2 To lastRow: ws.Cells(r, 3).Value = ... touches the worksheet once per row and is the number-one reason macros crawl. The mature move is to use Cells to find the corners, build the Range, and act on the whole block once — assign an array to Range(...).Value, or call a native method (see WorksheetFunction). Cells is how you locate the work; it should not be how you do every unit of it.

How ExcelMaster helps

Cells versus Range hides more decisions than it looks like it should: which argument is the row, whether a bare Cells just touched the whole sheet, whether a linear index runs across or down, and whether a loop should have been a single whole-range write. Each wrong turn is silent — a value one axis off, a sheet-wide reformat, a macro that crawls.

ExcelMaster lets you describe the result instead. Say "fill column C with a running total down to the last row of data," and it finds the last row, builds Range(Cells(2, 3), Cells(lastRow, 3)), and writes the values in one pass — row-first arguments correct, no accidental whole-sheet Cells, no per-cell loop. You keep the workbook and the code; you skip the pass where the writes landed transposed.

Frequently asked questions

What is the difference between Cells and Range in Excel VBA?

They both refer to cells, but they address them differently. Range("A1") uses a string in spreadsheet notation — column letter then row. Cells(1, 1) uses two numbers — row then column. Because Cells arguments are numbers you can compute, Cells is what you use inside loops and for positions worked out at runtime; Range is for fixed or named regions you type by name. They combine as Range(Cells(r1, c1), Cells(r2, c2)) to build a block from computed corners.

Why is Cells(1, 2) cell B1 and not A2?

Because Cells takes its arguments row first, column second — the opposite of the "letter then number" order you read on the grid. Cells(1, 2) means row 1, column 2, which is B1. Cells(2, 1) means row 2, column 1, which is A2. Remember "R before C" (row, then column), the same order as R1C1 notation.

How do I select a range of cells using Cells in VBA?

Pass two Cells calls as the corners of a Range: Range(Cells(1, 1), Cells(10, 4)) is A1:D10. This is the standard way to build a block when the corners are computed, for example Range(Cells(2, 1), Cells(lastRow, lastCol)). Qualify both Cells and the Range with the same worksheet so the reference does not point at whatever sheet happens to be active.

What does Cells with no arguments do in VBA?

A bare Cells (with no index) refers to every cell on the worksheet. Cells.ClearContents clears the whole sheet and Cells.Font.Size = 11 sets every cell. That is useful for sheet-wide changes, but it also means a Cells that lost its (row, column) index will silently act on the entire sheet — so always index it when you mean a specific cell.

Can I use a column letter with Cells instead of a number?

Yes. The column argument accepts a string, so Cells(2, "B") is the same as Cells(2, 2) (cell B2). The row argument must be a number. Using a letter is convenient when you know the column but compute the row, such as Cells(lastRow + 1, "C").

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-08-15.

Related guides: VBA Range · VBA Resize · VBA CurrentRegion · VBA Offset · VBA For Loop