The Range Object

One object for one cell or a million

Range is how VBA refers to cells. You hand it the same address you would type in the Name Box, and you get back an object you can read from, write to, format or clear. A Range can be a single cell, a block, an entire column, or several separate areas at once.

Range("A1")                ' one cell
Range("A1:C10")            ' a block
Range("A:A")               ' a whole column
Range("3:3")               ' a whole row
Range("A1:A5,C1:C5")       ' two separate areas
Range("SalesTotal")        ' a named range

Two corners instead of a string

Range also accepts two arguments — a top-left and a bottom-right cell — which is far more useful once those corners come from variables:

Sub TwoCornerRange()
    Dim lastRow As Long
    lastRow = 250

    Range("A2", Range("D" & lastRow)).Interior.Color = vbYellow
End Sub

Building an address from a variable

Because the address is just text, you can build it with the ampersand. This is the everyday way to point at a row whose number your code worked out:

Dim r As Long
r = 7
Range("B" & r).Value = "Row seven"   ' writes to B7

Things you can do to a Range

• .Value — read or write the contents.
• .Formula — read or write a formula as text.
• .ClearContents — wipe values, keep formatting.
• .Clear — wipe everything, formatting included.
• .Copy and .Delete — exactly what they sound like.
• .Rows.Count and .Columns.Count — how big it is.

Watch the quotation marks

Range("A1") with quotes is a cell address. Range(A1) without them makes VBA look for a variable named A1, which under Option Explicit gives you an immediate error — and without it, a silent failure. The quotes are not optional.

A range can describe itself

Every range knows its own address and size. Debug.Print Range("A1:C3").Address returns $A$1:$C$3, while .Rows.Count, .Columns.Count and .Cells.Count report its dimensions. These are invaluable when you build a range out of variables: print the address in the Immediate window and you can see instantly whether you got the block you intended.