📎 This article includes 1 downloadable practice file ↓

📘 Excel VBA Course · Lesson 2 of 15 — see all lessons

Almost every macro reads or writes cells, and in VBA a cell or block of cells is a Range. Master a handful of Range techniques and most automation becomes straightforward.

Range vs Cells

Range("B2").Value = 100          ' address as text
Cells(2, 2).Value = 100           ' row 2, column 2 — same cell
Range("A1:C10").Interior.Color = vbYellow
Range(Cells(1, 1), Cells(10, 3)).ClearContents

Use Range("…") for fixed addresses and Cells(row, col) inside loops where the row or column is a variable.

Always say which sheet

With ThisWorkbook.Worksheets("Data")
    .Range("A1").Value = "Report"
    .Cells(2, 1).Value = Date
End With

An unqualified Range refers to whatever sheet is active at that moment. Qualifying with a sheet (and the leading dot inside With) removes a whole class of bugs.

Offset: move relative to a cell

Range("A1").Offset(1, 0).Value = "below A1"   ' A2
Range("A1").Offset(0, 2).Value = "two right"   ' C1

Resize: grow a range

Range("A2").Resize(5, 3).Select   ' A2:C6

Offset + Resize together are perfect for “the data below the header”: rng.Offset(1).Resize(rng.Rows.Count - 1).

CurrentRegion: the block around a cell

Dim data As Range
Set data = Range("A1").CurrentRegion      ' like Ctrl+A inside a table
MsgBox data.Address & " has " & data.Rows.Count & " rows"

Rows, columns and counting

Rows(5).Delete
Columns("D").AutoFit
Range("A1:A100").Rows.Count          ' 100
Application.WorksheetFunction.CountA(Range("A:A"))   ' non-empty cells

Value, Text and Formula

Fast bulk read/write

Dim v As Variant
v = Range("A2:D5001").Value      ' 5,000 rows into memory in one step
' ...work on v(r, c)...
Range("A2:D5001").Value = v      ' write back in one step

Finding where data ends is covered in 5 ways to find the last row.

Try it yourself: step by step

1. Open the practice workbook with the course macros imported.
2. In the Immediate window (Ctrl+G) type ? Worksheets("Data").Range("A1").CurrentRegion.Address → $A$1:$H$61.
3. Type Worksheets("Data").Range("A2").Resize(5, 8).Select and press Enter — the first five data rows are selected.
4. Try ? Worksheets("Data").Range("A1").Offset(3, 2).Value to read the region of the third invoice.
5. Write a macro that copies the whole CurrentRegion to a new sheet using one line: rng.Copy Worksheets.Add.Range("A1").