The Range Object

One object for one cell or a million

Range is how VBA points at cells. You hand it the address you would type into the Name Box and you get back an object you can read from, write to, format, clear or measure. A Range can be a single cell, a rectangular block, a whole column, or several separate areas at once — and once you have one, everything you can do to cells by hand you can do to it in code.

Range("A1")                ' one cell
Range("A1:C10")            ' a block
Range("A:A")               ' a whole column
Range("3:3")               ' a whole row
Range("A1", "C10")         ' the same block, given as two corners
Range("SalesTotal")        ' a named range

A macro that shows all of it working

Paste this into a module (Alt+F11 → Insert → Module) and press F5. It creates its own sheet and its own data, so nothing on your existing sheets is touched, and each run makes a fresh tab.

Option Explicit

Sub RangeObjectTour()
    Dim ws As Worksheet
    Dim block As Range
    Dim dataRows As Long
    Dim lastRow As Long
    Dim i As Long
    Dim total As Double

    dataRows = 15

    Set ws = ThisWorkbook.Worksheets.Add
    ws.Name = "Range tour " & Format(Now, "hh-mm-ss")

    ' --- sample data, so the macro depends on no file of yours ---
    ws.Range("A1:C1").Value = Array("Order", "Customer", "Amount")

    For i = 1 To dataRows
        ws.Cells(i + 1, 1).Value = 1000 + i
        ws.Cells(i + 1, 2).Value = "Customer " & Chr(64 + i)
        ws.Cells(i + 1, 3).Value = 80 * i - 150 * (i Mod 3)
    Next i

    lastRow = dataRows + 1

    ' --- one block, held in a variable ---
    Set block = ws.Range("A2", ws.Range("C" & lastRow))
    block.Interior.Color = RGB(248, 250, 252)
    block.Borders.Color = RGB(203, 213, 225)

    ' --- reading the block back ---
    total = Application.WorksheetFunction.Sum(ws.Range("C2:C" & lastRow))

    ws.Range("E1").Value = "Report"
    ws.Range("E2").Value = "Rows"
    ws.Range("F2").Value = block.Rows.Count
    ws.Range("E3").Value = "Address"
    ws.Range("F3").Value = block.Address
    ws.Range("E4").Value = "Total"
    ws.Range("F4").Formula = "=SUM(C2:C" & lastRow & ")"
    ws.Range("E5").Value = "Largest"
    ws.Range("F5").Formula = "=MAX(C2:C" & lastRow & ")"

    ws.Range("A1:C1").Font.Bold = True
    ws.Range("E1:E5").Font.Bold = True
    ws.Range("C2:C" & lastRow).NumberFormat = "#,##0.00"
    ws.Columns("A:F").AutoFit

    Debug.Print block.Address & " holds " & block.Cells.Count & _
        " cells and sums to " & Format(total, "#,##0.00")

    MsgBox "Wrote " & dataRows & " rows into " & block.Address & ".", _
        vbInformation, "Range tour"
End Sub

Line by line

• Dim block As Range plus Set block = … — a Range goes into a variable like anything else, but objects need Set. Forget it and you get Object variable or With block variable not set.
• ws.Range("A2", ws.Range("C" & lastRow)) — the two-corner form. Top-left and bottom-right, which is what you want the moment a corner comes from a variable rather than from you.
• "C2:C" & lastRow — an address is just text, so you build it with the ampersand. With lastRow = 16 this becomes C2:C16.
• ws.Range("A1:C1").Value = Array(…) — one statement fills three cells; a Range does not have to be one cell to accept a value.
• .Value reads or writes contents, .Formula writes a formula as text, .NumberFormat changes only how it looks, .Interior.Color and .Borders.Color paint it.
• .Address, .Rows.Count, .Cells.Count — a range describes itself. Print them in the Immediate window (Ctrl+G) and you can see at once whether you built the block you meant to.

Change it and run again

Set dataRows to 200 — every address in the macro adapts, because none of them is typed out in full. Then swap block.Interior.Color for block.ClearContents and watch the difference between .Clear (values and formatting) and .ClearContents (values only).

The trap: a range with a comma is not one range

Range("A2:A5,C2:C7") looks like a neat way to grab two blocks at once, and formatting really does apply to all ten cells. But it is a multi-area range, and half of Excel disagrees with you about what that means:

Dim multi As Range
Set multi = ws.Range("A2:A5,C2:C7")

Debug.Print multi.Areas.Count          ' 2  — two separate blocks
Debug.Print multi.Cells.Count          ' 10 — all ten cells
Debug.Print multi.Rows.Count           ' 4  — the FIRST area only, silently
Debug.Print multi.Areas(2).Rows.Count  ' 6  — how to ask properly

multi.Interior.Color = vbYellow        ' fine: colours all ten cells
multi.Sort Key1:=ws.Range("A2")        ' run-time error 1004
multi.Copy ws.Range("H2")              ' run-time error 1004

Why that costs an hour

The silent line is the expensive one. .Rows.Count, .Row, .Column and .Value all report on the first area only and never warn you, so a loop built on that count processes four rows and ignores six. Sort, Copy and PasteSpecial at least fail loudly with That command cannot be used on multiple selections. Treat a comma in an address as a formatting convenience, and go through .Areas whenever you need real work done on each block.

How it goes on

This lesson always spelled out the sheet, ws.Range(…). A bare Range("A1") means whichever sheet happens to be in front, which is where most broken macros break — the last lesson of this course deals with that properly. In between you get Cells(row, column) for addressing by number, why selecting a range is almost never necessary, Offset and Resize for moving a reference around, and the line every real macro needs: finding the last used row instead of guessing at 15.