VBA Cells and Range: UsedRange and CurrentRegion

Q: What is the difference between Range and Cells in VBA?

Range takes an address string such as A1:D10 and can refer to blocks; Cells takes a row and column number and refers to one cell. Cells is preferred inside loops because the indexes can be variables.

Part of the free Module 12: Excel VBA Course · Lesson 10 of 18 · Full Excel course

Cells and Range are the VBA objects that point at the worksheet grid: Range addresses cells by name (“A1:D10”), Cells addresses them by row and column number, and properties such as Rows, Columns, UsedRange, CurrentRegion and SpecialCells select whole structures in one call. In this lesson you will learn how to reference cells and ranges in VBA reliably, find the last used row and work with dynamic data.

Table of Contents

Why referencing matters

Nearly every macro reads from or writes to cells, so the way you reference them decides whether the code is fast, readable and safe when the data grows. Always qualify a range with its worksheet, sh.Range("A1"), so it does not depend on which sheet happens to be active, and avoid Select followed by Selection in favour of acting on the range directly. The examples below use Select only so you can watch the result on screen.

Cells: row and column numbers

The syntax is Cells(RowIndex, ColumnIndex). Cell B3 is row 3, column 2; the column can also be given as a letter.

Sub Select_a_Cell()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")
    sh.Cells(3, 2).Select        'B3
    sh.Cells(3, "B").Value = 100 'same cell, written directly
End Sub

Numeric indexes make Cells ideal inside loops, where the row or column is a variable.

Columns and Rows

Sub Select_Columns_And_Rows()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")
    sh.Columns(2).Select                    'column B by number
    sh.Columns("B:B").Select                'column B by letter
    sh.Cells(1, 2).EntireColumn.Select      'column that contains B1
    sh.Rows(2).Select                       'row 2
    sh.Rows("2:2").Select                   'row 2, string form
    sh.Cells(2, 1).EntireRow.Select         'row that contains A2
End Sub

Columns and Rows return whole columns or rows; EntireColumn and EntireRow expand any cell to its full column or row, which is how you autofit or delete around a found cell.

Range: one cell or a block

Sub Select_a_Range()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")
    sh.Range("A1:D10").Select
    sh.Range(sh.Cells(1, 1), sh.Cells(10, 4)).Select   'same block
    sh.Range("A1", "D10").Interior.Color = vbYellow
End Sub

Range accepts an address string or two corner cells; the second form lets you build the block from variables.

Two more properties turn a fixed reference into a moving one. Offset(rows, cols) shifts the reference: sh.Range("A1").Offset(1, 0) is A2. Resize(rows, cols) changes its size: sh.Range("A1").Resize(10, 4) is A1:D10.

Selection, UsedRange and CurrentRegion

Sub Selection_Range()
    Selection.Interior.Color = vbRed
End Sub

Selection is whatever the user has highlighted; it is useful for utilities that act on the current choice.

Sub Used_Range()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")
    sh.UsedRange.Interior.Color = vbRed
End Sub

UsedRange is the rectangle from the first to the last cell that has ever held data or formatting on the sheet. It is convenient but can include stale empty rows.

Sub Current_Region()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")
    sh.Range("A1").CurrentRegion.Interior.Color = vbRed
End Sub

CurrentRegion is the block of contiguous data around a cell, the same range Ctrl+A selects, and is the most reliable way to grab a table.

SpecialCells

Sub Special_Cells()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")
    sh.UsedRange.SpecialCells(xlCellTypeFormulas).Select
End Sub

SpecialCells filters a range to cells of one kind: xlCellTypeFormulas, xlCellTypeConstants, xlCellTypeBlanks, xlCellTypeVisible, xlCellTypeLastCell and others. Wrap it in error handling, because it raises error 1004 when no cell matches.

Find the last row and loop through data

Sub Total_Sales_Column()
    Dim sh As Worksheet
    Dim lastRow As Long, i As Long
    Dim total As Double

    Set sh = ThisWorkbook.Sheets("Sheet1")
    lastRow = sh.Cells(sh.Rows.Count, "C").End(xlUp).Row   'last filled cell in column C

    For i = 2 To lastRow
        If IsNumeric(sh.Cells(i, "C").Value) Then
            total = total + sh.Cells(i, "C").Value
            sh.Cells(i, "D").Value = total                    'running total
        End If
    Next i

    With sh.Cells(lastRow + 1, "C")
        .Value = total
        .Font.Bold = True
        .Offset(0, -1).Value = "Total"
    End With
    sh.Range("A1").CurrentRegion.Columns.AutoFit
End Sub

This runnable macro finds the last row with Ctrl+Up logic, builds a running total in column D with Cells, writes a bold grand total below the data with Offset, and autofits the table using CurrentRegion.

Tips and common mistakes

Unqualified Range in a standard module refers to the active sheet; in a sheet module it refers to that sheet. Qualify it to avoid surprises.
Range(“A1”).End(xlDown) stops at the first blank; use Cells(Rows.Count, col).End(xlUp) from the bottom to find the real last row.
UsedRange may start below row 1 if the top rows are empty; use UsedRange.Rows.Count with care.
Read a range into an array (arr = sh.Range("A1:D1000").Value) when processing thousands of cells; it is many times faster than cell-by-cell access.
Merged cells return their value only from the top-left cell; avoid them in data areas.

Practice and real-world use

Write a macro that colours every blank cell in the CurrentRegion yellow using SpecialCells, then another that copies the last row of a table to the row below with Offset. Dynamic ranges, last-row detection and table blocks are the foundation of every data-entry form and report generator.

Watch the step-by-step video tutorial

Click here to download the practice file.

Related lessons

Frequently asked questions

What is the difference between Range and Cells in VBA?

Range takes an address string such as “A1:D10” and can refer to blocks; Cells takes a row and column number and refers to one cell. Cells is preferred inside loops because the indexes can be variables.

How do I find the last row with data in VBA?

Use lastRow = sh.Cells(sh.Rows.Count, “A”).End(xlUp).Row. It starts at the bottom of column A and moves up to the last filled cell, so blank rows inside the data do not stop it.

What is the difference between UsedRange and CurrentRegion?

UsedRange covers everything on the sheet that has ever been used, including stray formatted cells. CurrentRegion is the contiguous block of data around one cell, so it is usually the more accurate choice for a table.

Want the finished version? Ready-made Excel dashboards, trackers and VBA systems are available at NextGenTemplates.com.