VBA Range in Excel — Reference, Read & Write Cells (8 Examples)

Tested on: Excel 365 v2509 · Excel 2021 · Excel 2019 · last verified 2026-06-05

TL;DR — Range is the object that points at one or more cells. Read it with .Value, write it the same way. No .Select needed:

Sub RangeBasics()
    ' READ a single cell
    Dim qty As Long
    qty = Range("B2").Value

    ' WRITE a single cell
    Range("C2").Value = qty * 1.1

    ' WRITE a whole block at once (no loop)
    Range("A1:C1").Value = Array("Product", "Qty", "Price")
End Sub

Everything else — Cells, End, CurrentRegion, Resize — is just a different way to hand Range the cells you mean. This guide shows the eight you'll actually use.

All examples assume a small table in Sheet1: headers Product / Qty / Price in A1:C1, data starting in row 2.

What the Range object actually is

Range is the single most-used object in Excel VBA. It represents a cell, a block of cells, a whole row or column, or even several disconnected blocks. Almost everything you do — read a value, write a formula, change a colour, copy, sort — is a method or property you call on a Range.

Range("A2").Value = "Widget"      ' a property of one cell
Range("A2:C2").Interior.Color = vbYellow  ' a property of three cells
Range("A:A").Columns.AutoFit       ' a property of a whole column

Example 1 — Range vs Cells (when to use which)

Both point at cells. The difference is how you address them.

	Best for	Syntax
`Range("A2")`	A fixed, human-readable address	text address, like the Name Box
`Cells(2, 1)`	A position computed from numbers	`Cells(row, column)` — row first

' These two lines touch the SAME cell (row 2, column 1):
Range("A2").Value = "Widget"
Cells(2, 1).Value = "Widget"

Use Range when you know the address. Use Cells inside loops, where the row or column is a variable:

Dim r As Long
For r = 2 To 6
    Cells(r, 3).Value = Cells(r, 2).Value * 1.1   ' Price = Qty * 1.1
Next r

You can even combine them — Range(Cells(2, 1), Cells(6, 3)) builds the block A2:C6 from two corner cells, which is perfect when the corners are calculated.

Example 2 — `.Value` vs `.Value2` vs `.Text` vs `.Formula`

This is the trap that burns every beginner. The same cell gives you four different things:

' Cell B2 shows "$1,250.00" from the formula =A2*5
Debug.Print Range("B2").Value     ' 1250    (a Currency/Date-aware Variant)
Debug.Print Range("B2").Value2    ' 1250    (a raw Double — fastest, no Currency/Date)
Debug.Print Range("B2").Text      ' "$1,250.00"  (what the user SEES, as a string)
Debug.Print Range("B2").Formula   ' "=A2*5"  (the formula text)

Rules of thumb:

.Value2 — use it when you read large ranges or do maths. It skips the Currency/Date conversion, so it's faster and never surprises you with a rounded Currency value.
.Text — read-only-ish, returns the displayed string. Beware: if the column is too narrow it returns "####".
.Formula — read or write the formula, not the result.

Example 3 — Stop using `.Select` (the #1 anti-pattern)

The macro recorder writes .Select everywhere. Real macros don't. Selecting is slow, flickers the screen, and breaks the moment the wrong sheet is active.

' ❌ Recorder style — fragile and slow
Range("A2").Select
Selection.Value = "Widget"

' ✅ Act on the Range directly
Range("A2").Value = "Widget"

Qualify the sheet instead of selecting it, so the code works no matter what's on screen:

ThisWorkbook.Worksheets("Sheet1").Range("A2").Value = "Widget"

Example 4 — Find the last row (the canonical idiom)

Hard-coding A2:A100 breaks when the data grows. This one line finds the real last used row in column A:

Dim lastRow As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row    ' jump up from the very bottom
MsgBox "Data ends on row " & lastRow

End(xlUp) mimics pressing Ctrl + ↑. It's the most reliable way to size a range to your data.

Example 5 — Dynamic ranges with `CurrentRegion`

CurrentRegion grabs the whole contiguous block around a cell — exactly what Ctrl + Shift + ↑↓←→ selects. No row counting needed:

Dim data As Range
Set data = Range("A1").CurrentRegion        ' the entire table, headers and all
MsgBox data.Rows.Count & " rows × " & data.Columns.Count & " columns"

' Skip the header row — Offset down one, Resize one shorter:
Dim body As Range
Set body = data.Offset(1, 0).Resize(data.Rows.Count - 1)

Offset(rows, cols) shifts a range; Resize(rows, cols) changes its size. Together they let you carve a header off, add a column, or grow a block — all without a single hard-coded address.

Example 6 — Loop a range the readable way

When you want each cell, For Each is cleaner than index maths:

Dim cell As Range
For Each cell In Range("B2:B6")
    If cell.Value > 100 Then cell.Interior.Color = vbGreen
Next cell

Example 7 — Read a whole range into an array (the fast way)

Here's the secret pros use: touching the worksheet thousands of times is slow. Read the entire range into memory in one shot, work there, then write back once. On 10,000 rows this is the difference between 8 seconds and 0.1:

Sub FastRange()
    Dim arr As Variant
    arr = Range("A2:C10000").Value2          ' ONE read -> 2D array (1-based)

    Dim i As Long
    For i = 1 To UBound(arr, 1)
        arr(i, 3) = arr(i, 2) * 1.1          ' work in memory, no cell touches
    Next i

    Range("A2:C10000").Value2 = arr          ' ONE write
End Sub

That arr = SomeRange.Value2 trick is so important it has its own guide.

Example 8 — Non-contiguous ranges and named ranges

A Range can hold several disconnected blocks (a Union), and you can address a named range by its name:

' Two separate blocks, coloured together:
Range("A2:A6, C2:C6").Interior.Color = vbCyan

' A range you named "TaxRate" in the Name Box:
Range("TaxRate").Value = 0.2

Common Range mistakes (and the fix)

Symptom	Cause	Fix
Wrong sheet gets edited	`Range(...)` with no sheet qualifier uses the active sheet	Prefix `ThisWorkbook.Worksheets("Sheet1").Range(...)`
`Cells(1, 2)` hits the wrong cell	`Cells` is (row, column), not (column, row)	Row number comes first
Loop is painfully slow	Reading/writing each cell separately	Load into an array (Example 7)
`.Text` returns `"####"`	Column too narrow to display the value	Read `.Value2` instead of `.Text`
Range stops short after data grows	Hard-coded `A2:A100`	Size with `End(xlUp)` or `CurrentRegion`

Stop hand-tuning Range plumbing — describe the result instead

Range, Cells, End, Resize — that's a lot of plumbing to move a column of numbers. ExcelMaster Agent lets you say it in plain English — "in Sheet1, multiply every Qty in column B by 1.1 and put the result in column C, for as many rows as there are" — and it writes range-safe code that already handles the last row, the header, and the active-sheet trap. Try it free →

FAQ

What is the difference between Range and Cells in VBA? Both point at cells. Range("A2") uses a text address; Cells(2, 1) uses numeric (row, column) coordinates — ideal inside loops where the position is a variable. Range(Cells(2,1), Cells(6,3)) combines them to build a block from calculated corners.

Should I use .Value or .Value2 in VBA? Use .Value2 for reading large ranges and doing maths — it returns a raw Double, skips Currency/Date conversion, and is faster. Use .Value when you specifically need Currency or Date typing.

How do I select a range with a variable row and column? Use Cells(row, col) with variables, or Range(Cells(r1, c1), Cells(r2, c2)) for a block. To size a range to your data, find the last row with Cells(Rows.Count, 1).End(xlUp).Row.

How do I reference a whole table without knowing its size? Range("A1").CurrentRegion returns the entire contiguous block. To drop the header row, use .Offset(1, 0).Resize(.Rows.Count - 1).

Why should I avoid .Select in VBA? .Select is slow, makes the screen flicker, and depends on the right sheet being active. Act on the range directly — Range("A2").Value = "x" — and qualify it with the worksheet to make it robust.