Working with Ranges in Excel VBA: Cells, Offset, Resize and More

📎 This article includes 1 downloadable practice file ↓

Almost every macro reads or writes cells, so the Range object is the one you’ll use most. This lesson covers the handful of ways to point at cells that cover nearly every real situation — and the one habit (Select/Activate) to drop early.

Range and Cells

Range("A1").Value = "Name"            ' by address
Range("A2:C10").ClearContents         ' a block
Cells(2, 3).Value = 500               ' row 2, column 3 = C2
Cells(2, "C").Value = 500             ' same, column by letter
Range(Cells(2, 1), Cells(10, 3)).Font.Bold = True   ' block built from two corners

Range("...") is easiest to read for fixed addresses. Cells(row, col) is better inside loops, because the row is a number you can change.

Always say which sheet

Range("A1") on its own means “A1 on whatever sheet is active”. That’s the root of the classic “it worked yesterday” bug. Put the sheet in front:

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Data")
ws.Range("A1").Value = "Report"
With ws
    .Range("B2").Value = Date
    .Cells(3, 2).Value = "Prepared by " & Environ$("USERNAME")
End With

The With block saves repeating ws. — note the dot before each Range.

Finding the last row (the right way)

lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

This starts at the very bottom of column A and jumps up to the last filled cell — like pressing Ctrl+↑ from row 1,048,576. Pick a column that is always filled (an ID or date column), not one with gaps.

Offset: move relative to a cell

Set c = ws.Range("B5")
c.Offset(0, 1).Value = "next column"      ' C5
c.Offset(1, 0).Value = "next row"         ' B6
c.Offset(-1, -1).Value = "up and left"    ' A4

Offset is perfect for “the cell next to the one I found”.

Resize: make a range bigger or smaller

ws.Range("A2").Resize(10, 3).Interior.Color = RGB(235, 245, 255)   ' A2:C11
ws.Range("A1").CurrentRegion.Offset(1).Resize( _
    ws.Range("A1").CurrentRegion.Rows.Count - 1).Select           ' data without header

Resize is how you write an array of results back in one go (lesson 9) — Range("F2").Resize(rows, cols).Value = arr.

CurrentRegion and UsedRange

• Range("A1").CurrentRegion — the block around A1 up to the first fully blank row and column. Ideal for a tidy table.
• ws.UsedRange — everything Excel thinks has ever been used, including formatted empty cells. Handy, but can be bigger than you expect.

Value, Value2, Formula and Text

Stop selecting things

The macro recorder writes code like this:

Range("B2").Select
Selection.Copy
Sheets("Report").Select
Range("D5").Select
ActiveSheet.Paste

It works, but it’s slow, flickers, and breaks when anything else has focus. The same thing in one line:

ThisWorkbook.Worksheets("Data").Range("B2").Copy ThisWorkbook.Worksheets("Report").Range("D5")
' or values only:
ThisWorkbook.Worksheets("Report").Range("D5").Value = ThisWorkbook.Worksheets("Data").Range("B2").Value

Where people go wrong

• Wrong sheet changed — unqualified Range(). Always use a sheet variable.
• Last row is 1 — the column you checked is empty; pick an always-filled column.
• Error 1004 on Range — address typed wrongly ("A1:C") or a Cells reference on another sheet inside Range(Cells(), Cells()) without qualifying both.

Practice

The workbook has exercises for each method — find the last row, fill the column next to found values, copy a block with Resize — and a recorded macro full of Select statements for you to clean up.

Next: formatting cells with VBA — fonts, colours, borders and number formats.