VBA Offset in Excel — Move Relative to a Cell (Offset vs. Resize and the Off-by-One Trap)

TL;DR — rng.Offset(rowDelta, colDelta) returns a new range shifted by that many rows and columns from rng. It's a step, not a place: it keeps the same size, doesn't change rng, and doesn't select anything. The two numbers are relative — Offset(1, 0) is one row down, Offset(0, 0) is the cell itself, and negatives go up/left. Offset moves; Resize changes size. Put them together and you get the single most useful range idiom in VBA: data.Offset(1).Resize(data.Rows.Count - 1) — "everything except the header."

Offset is how a macro says "the cell next to the one I just found." You located a match, a total, a header — now you want to read or write the cell one column over or one row down. That relative move is Offset's whole job, and it becomes second nature once you stop reading the arguments as coordinates and start reading them as steps.

What you'll learn

The mental model: Offset is a relative step, not an absolute address
The off-by-one trap — why Offset(1, 1) lands on B2, not A1
Offset keeps the size; Resize changes it — and why you usually need both
The header-skip idiom Offset(1).Resize(n - 1), explained
How Range.Offset (VBA) differs from the OFFSET() worksheet function

The mental model: Offset is a step, not a place

Stand on a cell. Offset(rowsDown, colsRight) takes that many steps and hands you the cell (or block) you land on — a brand-new range. The original is untouched. Because the move is relative, the same Offset(1, 0) means "one row down" whether you start at A1 or Z500.

Sub OffsetSteps()
    Debug.Print Range("B2").Offset(1, 0).Address   ' -> $B$3   (down one row)
    Debug.Print Range("B2").Offset(0, 1).Address   ' -> $C$2   (right one column)
    Debug.Print Range("B2").Offset(-1, 0).Address  ' -> $B$1   (up one row)
    Debug.Print Range("B2").Offset(0, 0).Address   ' -> $B$2   (no move)
End Sub

Notice Offset(0, 0) is the cell itself. That's the tell that these are deltas, not positions. If you want an absolute cell, don't compute an offset from A1 — use Cells(row, col). Offset is for "relative to something I already have."

The rule that catches beginners: the off-by-one trap

The number-one Offset bug is reading the arguments as a coordinate. Offset(1, 1) does not mean cell A1 or "row 1, column 1." It means one row down and one column right from the anchor. From A1 that lands on B2.

Range("A1").Offset(1, 1).Address   ' -> $B$2, NOT $A$1
Range("A1").Offset(0, 0).Address   ' -> $A$1

Say the two arguments out loud as "down, right" every time and the bug disappears. It also explains the classic loop pattern: to write a result next to each row you found, you offset by the loop counter, and the first row uses Offset(0, …), not Offset(1, …).

The distinction that trips everyone: Offset moves, Resize changes size

Offset never changes how big a range is — it slides the whole shape. Move a ten-cell column and you get a ten-cell column somewhere else:

Range("A1:A10").Offset(0, 1).Address   ' -> $B$1:$B$10   (10 cells, moved right)

To change the size of a range you need a different method, Resize(rows, cols), which redraws the block from its top-left cell:

Range("A1").Resize(5, 2).Address       ' -> $A$1:$B$5   (5 rows, 2 columns)

They're the two halves of range arithmetic: Offset picks where, Resize picks how big. You reach for both together constantly — most often to drop a header row.

The idiom worth memorizing: skip the header with Offset + Resize

You have a table including its header, and you want just the data body. Offset down one row to move off the header — but Offset alone keeps the same height, so the range now runs one row past the data. Resize shrinks it back by that one row:

Sub DataBodyOnly()
    Dim tbl As Range, body As Range
    Set tbl = Range("A1").CurrentRegion          ' header + data
    Set body = tbl.Offset(1).Resize(tbl.Rows.Count - 1)
    body.Select                                  ' data rows only, no header
End Sub

Offset(1) (the column delta defaults to 0) slides the whole block down one row; Resize(tbl.Rows.Count - 1) trims the now-overhanging bottom row. The result is the data body with the header removed — the exact range you want to sort, copy, or clear. This one line replaces a lot of manual A2:A string-building. To find tbl without hardcoding, see finding the last row and CurrentRegion.

The edge-of-sheet trap

Because Offset is relative, stepping past the edge of the worksheet is an error, not a silent clamp:

Range("A1").Offset(-1, 0)   ' run-time error 1004 — can't go above row 1

If a macro walks up or left near the top-left corner (common when scanning for headers), guard the move or check rng.Row > 1 before offsetting upward.

Offset the method vs. OFFSET the worksheet function

They share a name and the same idea, but they are not the same tool. The worksheet function OFFSET() lives in a cell, is volatile (it recalculates on every change, which can slow a workbook), and returns a value or reference for a formula. Range.Offset is a VBA method: it returns a Range object, is not volatile, and runs only when your code runs. If you're writing a macro, use the method; reach for the worksheet function only when you need a live, self-updating reference inside a formula.

How ExcelMaster helps

Offset-heavy code is usually building one thing: a result placed relative to something you found — a flag next to each match, a running total one column over, a value copied from the row below a label. ExcelMaster takes that intent in plain English — "next to every overdue invoice, write the days late" — and generates the relative-reference logic, so you don't hand-count deltas or debug an off-by-one.

You'll still write Offset inside a tight macro loop. But for the everyday "put a result beside each row" task, describing the placement is faster and harder to get wrong than reasoning about (1, 0) versus (0, 1).

Frequently asked questions

What does Offset do in VBA?

rng.Offset(rowDelta, colDelta) returns a new range shifted by that many rows and columns from rng, keeping the same size. It's a relative move: Offset(1, 0) is one row down, Offset(0, 1) is one column right, and negative numbers move up or left. It doesn't change or select the original range.

Why does Offset(1, 1) not return cell A1?

Because the arguments are deltas, not coordinates. Offset(1, 1) means one row down and one column right from the anchor cell, so Range("A1").Offset(1, 1) is B2. Use Cells(row, col) when you want an absolute cell instead of a relative step.

What's the difference between Offset and Resize?

Offset moves a range without changing its size; Resize(rows, cols) changes the size from the top-left cell without moving it. You often combine them — rng.Offset(1).Resize(rng.Rows.Count - 1) skips a header row by moving down one and shrinking by one.

How do I skip the header row with Offset?

Use table.Offset(1).Resize(table.Rows.Count - 1). Offset(1) moves the block down past the header; Resize trims the row that now overhangs the bottom, leaving just the data body.

Is VBA Range.Offset the same as the OFFSET worksheet function?

No. Range.Offset is a VBA method that returns a Range object and is not volatile. The OFFSET() worksheet function lives in a cell, is volatile (recalculates constantly), and returns a value or reference for formulas. Use the method in macros; use the function only for live formula references.

Tested in

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