Excel VBA Range: select, read and write cells in a macro

Almost every macro reads or changes cells. In VBA that’s done with Range. Open the editor with Alt+F11 (how).

Read and write one cell

Range("A1").Value = 100
x = Range("B2").Value
Range("C1").Formula = "=SUM(A1:A10)"

A block of cells

Range("A2:D20").ClearContents
Range("A1:D1").Font.Bold = True
Range("B2:B20").NumberFormat = "#,##0.00"

On a specific sheet

Worksheets("Data").Range("A1").Value = "Report"

Without the sheet name, VBA uses whatever sheet is active — a common source of bugs.

Cells: numbers instead of letters

Cells(2, 3).Value = "Hi"   ' row 2, column 3 = C2

Perfect inside loops: Cells(i, 1). See VBA loops.

Find the last row with data

lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A2:A" & lastRow).Select

Avoid Select

Recorded macros are full of .Select then Selection.. Write straight to the range instead — it’s faster and doesn’t jump around:

' recorded
Range("A1").Select
Selection.Font.Bold = True
' better
Range("A1").Font.Bold = True

Useful properties

Start from zero: VBA tutorial