Skip to content
Tutorials Center
My learning

Topics

Lesson 21 of 37 ยท Working with Excel

Excel VBA: Ranges and Cells

Master the Range object in Excel VBA: reading and writing values, Cells, Offset, Resize, CurrentRegion, last row, Find, and fast loops over arrays.

The Range object is the workhorse of Excel VBA. A range can be one cell, a block, a whole column or several separate areas. Almost everything you do to data, reading it, writing it, formatting it, sorting it, goes through a Range.

This lesson shows the main ways to point at cells, how to find the last used row, and how to loop over data quickly. The most important performance idea comes at the end: read the whole block into an array, work in memory, and write it back.

VBA is read-only on this site. The runnable examples use JavaScript arrays to model what the VBA does with ranges and arrays.

Ways to point at cells

VB
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Data")

ws.Range("A1").Value = "Name"            ' A1 notation
ws.Range("A2:C10").Interior.Color = vbYellow
ws.Cells(2, 1).Value = "Ada"             ' Cells(row, column)
ws.Range(ws.Cells(2, 1), ws.Cells(10, 3)).ClearContents
ws.Columns("B").AutoFit
ws.Rows(1).Font.Bold = True

Cells(row, column) is ideal in loops because both numbers are variables. Range("A1:C10") is ideal for fixed addresses. You can also use a named range: ws.Range("TaxRate").Value.

Value, Value2, Text and Formula

Property Returns
Value The cell value; dates and currency come back as Date/Currency
Value2 The raw value; faster, no date/currency conversion
Text The formatted text as displayed ("1,234.50"), #### if the column is too narrow
Formula The formula text, such as =SUM(A1:A5)

Use Value2 for speed and for numbers, Text only when you need exactly what the user sees.

Moving around: Offset, Resize, End

VB
Dim c As Range
Set c = ws.Range("B2")

c.Offset(1, 0).Value = "below B2"          ' one row down
c.Offset(0, 2).Value = "two columns right"
c.Resize(3, 2).Interior.Color = RGB(220, 240, 255)   ' 3 rows x 2 columns
  • Offset(rows, cols) moves a range by a distance.
  • Resize(rows, cols) changes the size, keeping the top-left cell.
  • End(xlUp), End(xlDown), End(xlToLeft), End(xlToRight) behave like Ctrl+Arrow.
  • CurrentRegion returns the block of data around a cell bounded by empty rows and columns, like Ctrl+A on a table.

Finding the last row

VB
Function LastRow(ws As Worksheet, Optional col As Long = 1) As Long
    LastRow = ws.Cells(ws.Rows.Count, col).End(xlUp).Row
End Function

Start at the very bottom of the column and go up. This works even if there are gaps in the middle, unlike End(xlDown) from the top. ws.UsedRange is another option, but it can include formatted-but-empty cells.

Looping through cells

VB
Dim r As Long
For r = 2 To LastRow(ws)
    If ws.Cells(r, 3).Value2 > 100 Then
        ws.Cells(r, 3).Font.Color = vbRed
    End If
Next r

For Each cell In ws.Range("C2:C100") is also fine for short loops. Each cell touch is a call across the boundary between VBA and Excel, which is slow when done tens of thousands of times.

The array pattern (fast)

Reading a range into a Variant gives you a 2-D array, indexed (row, column) starting at 1.

VB
Sub DoubleTotals()
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Data")
    Dim data As Variant
    data = ws.Range("C2:C" & LastRow(ws)).Value2   ' one read

    Dim i As Long
    For i = LBound(data, 1) To UBound(data, 1)
        data(i, 1) = data(i, 1) * 2
    Next i

    ws.Range("C2").Resize(UBound(data, 1), 1).Value2 = data   ' one write
End Sub

Two cross-boundary calls instead of thousands: usually 10 to 100 times faster.

Find, Union and special cells

VB
Dim hit As Range
Set hit = ws.Columns("A").Find(What:="Ada", LookIn:=xlValues, LookAt:=xlWhole)
If Not hit Is Nothing Then
    Debug.Print "Found in row " & hit.Row
End If

Dim blanks As Range
On Error Resume Next
Set blanks = ws.Range("A2:A100").SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not blanks Is Nothing Then blanks.Interior.Color = vbYellow

Find returns Nothing when nothing matches, so always test before using the result. SpecialCells raises an error when there is no match, hence the short On Error block (see error handling).

Try it: a 2-D array and the last-row idea

JS 2-D array pattern: read, change, write back

Press Run (or Ctrl + Enter) to see the console output here.

No output.

JS LastRow with End(xlUp)

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Common mistakes

  • Using .Select and .Activate. You almost never need them: ws.Range("A1").Value = 5 works without selecting anything.
  • Writing Range("A1") without a sheet; it goes to whichever sheet is active.
  • Using End(xlDown) from the top on data with blank cells.
  • Looping cell by cell over big ranges instead of using an array.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does ws.Cells(5, 3) refer to?

    Cells takes row first and column second, so Cells(5, 3) is C5.
  2. 2. How do you reliably find the last used row in column A?

    Starting from the bottom of the sheet and going up is not fooled by gaps in the data.
  3. 3. Why is reading a range into an array faster than looping cells?

    Each cell access is a cross-process call; one bulk read and one bulk write replace thousands of them.
  4. 4. What does Find return when nothing matches?

    Find returns Nothing, so test with If Not hit Is Nothing before using it.

Summary

  • Reach cells with Range, Cells(row, col), named ranges, Offset, Resize and CurrentRegion.
  • Find the last row with Cells(Rows.Count, col).End(xlUp).Row.
  • Prefer Value2 and avoid Select/Activate.
  • For big data use the array pattern: one read, loop in memory, one write.
  • Find and SpecialCells can return nothing; handle that case.

Next: Excel VBA: Formulas and Formatting