Microsoft Excel VBA Range: Working With Cells and Ranges

In Excel VBA, a Range is an object that represents one cell, a rectangular block of cells, or—in some cases—multiple separate areas. Reference it with a worksheet-qualified expression such as Worksheets("Sheet1").Range("A1:B5"), then use properties like Value to read or change its contents.

What Range means in VBA

Excel’s grid is made up of cells, and VBA uses the Range object to work with those cells. A range can be as small as A1, as large as an entire column, or a block such as B2:D20. It can also refer to cells through a defined name. This makes Range one of the central objects in Excel automation: macros use it to inspect, enter, format, copy, or clear worksheet data.

Range describes which cells are involved; a property or method describes what VBA does with them. For example, Value gets or sets cell content, Font.Bold changes font emphasis, and ClearContents removes values and formulas without removing formatting. A Range reference does not necessarily mean that cells are selected on screen. VBA can work with a referenced range while the user is viewing another area.

Range is an object, so assigning a worksheet cell to an object variable uses Set. By contrast, assigning the cell’s value to a regular variable does not use Set. Keeping those two cases distinct avoids a common source of beginner errors.

Basic Range syntax and examples

The usual syntax is Worksheet.Range("address"). You can also use a range’s default worksheet context, but explicit qualification is safer in macros. The following example reads the content of cell B2 into a variable and displays it:

Sub ReadOneCell()
    Dim ws As Worksheet
    Dim cellValue As Variant

    ' Use the sheet in the workbook that contains this macro.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    cellValue = ws.Range("B2").Value
    MsgBox cellValue
End Sub

Variant is useful for a value that might be text, a number, a date, an error, or empty. If you know the expected data type, you can choose a more specific variable type, but a mismatch may require additional handling. The sheet name in the example must exist in the workbook; otherwise, VBA raises a run-time error.

A Range can also be assigned a value directly. With a worksheet variable named ws already set to the intended sheet, ws.Range("A1").Value = "Ready" writes text into A1. For multiple cells, a single value assignment fills every cell in the addressed range with that value. To transfer a two-dimensional array to a block in one operation, the array dimensions must match the target range’s rows and columns.

Use Cells and Range together

Range is convenient when the address is known, while Cells(row, column) is useful when row or column positions are calculated. The first argument to Cells is the row number and the second is the numeric column number: column A is 1, B is 2, and so on. You can combine the two to define a rectangle by its top-left and bottom-right cells.

Sub WriteCalculatedCell()
    Dim ws As Worksheet
    Dim targetRow As Long

    ' Set the worksheet and choose a row programmatically.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    targetRow = 5
    ws.Cells(targetRow, 2).Value = "Checked"
End Sub

This writes “Checked” to B5. The row number can come from a loop, a user choice, or a calculation. For a block whose bounds are stored in variables, use ws.Range(ws.Cells(firstRow, firstColumn), ws.Cells(lastRow, lastColumn)). Both Cells references should belong to the same worksheet; qualifying them through ws prevents VBA from accidentally using the active sheet.

Use a numeric column index in Cells for clarity. Although a cell address in Range can use a column letter, Cells is designed around row and column indexes, making it especially useful for loops and changing dimensions.

Qualify the worksheet and workbook

An unqualified expression such as Range("A1") refers to the active sheet in the active workbook. That can be the wrong sheet if a user clicks elsewhere, a macro activates another workbook, or an earlier line changes the active sheet. Qualifying the reference—ws.Range("A1")—makes the target explicit and usually makes a macro more dependable.

ThisWorkbook means the workbook containing the VBA code. ActiveWorkbook means the workbook currently active in Excel; those can differ, especially when a macro is stored in a personal macro workbook or an add-in. A common pattern is to assign the intended worksheet once, then use that variable consistently:

Sub SetStatus()
    Dim ws As Worksheet

    ' Refer to a specific sheet in the macro's workbook.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    ws.Range("C2").Value = "Complete"
End Sub

For a worksheet name containing spaces, keep the name inside the quoted string, as in Worksheets("Quarterly Report"). A sheet’s tab name is not interchangeable with its VBA code name, and the name must match the workbook’s actual sheet name.

Read, write, and change cell contents

The Value property is the standard way to read or write a cell’s underlying value. If a cell contains a formula, reading Value returns its calculated result, while Formula provides the formula expression. Excel also has Value2, which avoids the Currency and Date data subtypes returned by Value; it is often useful when transferring raw worksheet values to VBA variables or arrays.

These properties do not do the same job as formatting properties. To make a range bold, for example, set its Font.Bold property; changing Value does not change the font. Likewise, clearing only the contents leaves formatting in place, while clearing all cell properties is a different operation. Choose the method that matches the intended effect instead of using a broad clear operation by habit.

When writing a formula from VBA, the target cell’s Formula property can be assigned a formula string. Formula strings use Excel’s formula syntax and should be checked for correct references and separators. For data entry, a macro that writes to a range can replace existing content immediately, so verify the target address before running it on important sheets.

Build a range whose size changes

Many worksheets grow as rows are added, so hard-coding a final row can omit new records or include blank rows. A common approach is to find the last used row in a column that is reliably populated, then build the range from a starting row to that row. This example treats column A as the key column and reads its last populated row:

Sub FindLastDataRow()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim dataRange As Range

    ' Find the last nonempty cell in column A.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    If lastRow >= 2 Then
        Set dataRange = ws.Range(ws.Cells(2, 1), ws.Cells(lastRow, 3))
        MsgBox dataRange.Address
    Else
        MsgBox "No data rows were found."
    End If
End Sub

The example assumes headers are in row 1 and records occupy columns A through C. If column A can contain blanks below the final record, it may not identify the true final row; select a column that is consistently filled or use a different way to define the dataset. The check for row 2 also prevents the macro from treating the header alone as a data range.

Dynamic ranges can also be built from known start and end positions using Cells. This is often easier to maintain than joining address strings, particularly when both row and column boundaries are calculated at run time.

Range versus selection

A Range reference and a selection are related but not identical. ws.Range("A1").Value addresses A1 directly without selecting it. Select changes what is selected in the Excel interface and generally requires the relevant sheet to be active. Most macros should act on a qualified Range directly because selecting cells adds dependence on the user interface and can make code slower or more fragile.

Selection is appropriate when the macro’s purpose is to guide a person to a cell or visibly highlight a location. It is usually unnecessary for reading, writing, formatting, or clearing known cells. If a macro uses Selection, remember that its target depends on what the user or preceding code selected; it is not a fixed reference to a particular worksheet address.

Common Range errors and safer habits

“Application-defined or object-defined error” can have several causes, including a misspelled sheet name, an invalid address, or a reference to an object that is not available in the current context. “Object variable or With block variable not set” often means an object variable was declared but never assigned with Set. If code fails, check the highlighted line, confirm the workbook and worksheet, and verify that each variable points to the intended object.

Do not rely on the active sheet: qualify Range and Cells through a worksheet variable.
Check boundaries: confirm that calculated row and column numbers are positive and that the target range has the expected size.
Handle empty data: a last-row calculation can return the header row when there are no records.
Use the right variable: declare object references as Worksheet or Range and assign them with Set; use an appropriate data type for values.
Test on a copy: save a copy of the workbook before running a macro that changes cells. VBA actions cannot be undone with Ctrl+Z.

On Microsoft 365 for Windows, the Visual Basic Editor can be opened with Alt+F11. If the Developer tab is not shown, enable it through File > Options > Customize Ribbon and select Developer. Excel for Mac supports VBA, but keyboard shortcuts can depend on the keyboard and macOS settings; Option+F11 may require the Fn key. Excel for the web cannot create, run, or edit VBA macros, so use the desktop app for these examples.

Frequently Asked Questions

What is the difference between Range and Cells in VBA?
Range commonly identifies cells using an address such as A1:B5 or a defined name. Cells identifies a cell by numeric row and column indexes, which is handy when the coordinates are calculated in code. Both can be qualified by a worksheet and used together.

Do I need to select a cell before changing it?
No. A qualified reference such as ws.Range("A1").Value = 10 changes A1 directly. Selecting first is usually unnecessary and can make a macro depend on which sheet is active.

How do I refer to a whole column or row?
Use an address such as ws.Range("A:A") for a whole column or ws.Range("1:1") for a whole row. For operations that should affect only populated cells, calculate the relevant boundaries instead of processing an entire column without need.

Can Range refer to a named range?
Yes. A defined name can be supplied to Range, such as ws.Range("SalesTotal"), when that name is visible in the worksheet’s scope. Names can be workbook-scoped or worksheet-scoped, so ambiguous names should be checked in Name Manager and referenced in the correct sheet context.

Can Excel for the web run VBA?
No. Excel for the web can open a workbook containing macros, but it cannot run, create, or edit VBA code. Open the workbook in desktop Excel for Windows or Mac to work with VBA.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.