Excel VBA Range: How to Select, Read, and Edit Cells

VBA & macros · Updated November 18, 2026

In Excel VBA, a Range is an object that refers to one cell, a group of cells, or a named range. Use it to read or change cell contents and formatting; for example, Worksheets("Sheet1").Range("A1").Value refers to cell A1 on Sheet1.

What a Range object represents

A worksheet is made up of cells, and VBA uses the Range object as a way to address those cells. A range can be a single cell such as A1, a rectangular block such as A1:C10, or multiple separate areas. The object gives your code access to a cell or block’s values, formulas, formatting, and other properties.

The word “range” can also refer to the address written inside the parentheses, such as "B2:D8". That address is not itself the object: VBA evaluates it in the context of a worksheet and returns a Range object. Once you have that object, you can inspect or change its properties, or pass it to another method.

For example, a macro might read a customer name from one cell, write a result into another, or format a whole report section at once. Range is central to these tasks, but it is not a separate worksheet or a copy of the cell data. It points to cells in a workbook.

Basic Range syntax

The common form is Worksheet.Range("address"). The worksheet qualification matters: it tells VBA where to find the address. A Range expression without a worksheet qualifier, such as Range("A1"), uses the active sheet. That can produce unexpected results if the user has clicked another sheet before running the macro.

A complete macro that writes a heading to a specific worksheet looks like this. Before running any macro, save a copy of the workbook: changes made by VBA generally cannot be reversed with Ctrl+Z.

Sub WriteHeading()
    Dim ws As Worksheet

    ' Use the intended worksheet, not whichever sheet is active.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    ws.Range("A1").Value = "Monthly sales"
End Sub

ThisWorkbook means the workbook containing the macro. By contrast, ActiveWorkbook means the workbook that is currently active in Excel, which might be a different file. Use the first when the code should operate on its own workbook; use the second only when the active workbook is deliberately the target.

Range addresses can refer to one cell or a block. Common examples include "A1", "A1:B10", and "A:A" for an entire column. Whole-row references such as "1:1" are also possible. Large whole-column operations can be slow if the macro processes every cell, so limit the area when you can.

Use Cells for row-and-column references

When a row or column number changes during a macro, the Cells property is often clearer than constructing an address as text. Its arguments are row number first and column number second: Cells(3, 2) means B3. You can combine it with Range to define a rectangle between two cells.

Sub WriteByRowAndColumn()
    Dim ws As Worksheet
    Dim target As Range

    ' Cells uses row first, then column.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set target = ws.Cells(3, 2)
    target.Value = "Ready"
End Sub

To construct a range whose endpoints are calculated, use ws.Range(ws.Cells(firstRow, firstColumn), ws.Cells(lastRow, lastColumn)). Qualify both Cells references with the same worksheet. Otherwise, an unqualified Cells reference can use the active sheet and create a range that does not match the worksheet you intended.

For example, if firstRow is 2, lastRow is 12, firstColumn is 1, and lastColumn is 3, the resulting block is A2:C12. This approach is useful when the range size depends on the data, rather than being fixed in advance.

Read and write values

To read a cell, assign its Value or Value2 property to a variable. A Variant variable is a practical choice when a cell might contain text, a number, a blank, or an Excel error value. For ordinary values, either property can be useful; Value2 avoids VBA’s special Date and Currency subtypes and returns the underlying numeric value for dates.

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

    ' Read from the specified sheet.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    cellValue = ws.Range("A1").Value2
    MsgBox CStr(cellValue)
End Sub

Writing works in the opposite direction: assign a value to the property. Setting a cell’s Value or Value2 replaces its current contents, so it can overwrite a formula. If a cell contains a formula you need to preserve, check that before assigning a new value.

For a multi-cell range, VBA can transfer a two-dimensional array to or from the range in one operation, provided the array dimensions match the range dimensions. This is commonly faster than reading or writing one cell at a time. Assigning one constant value to a multi-cell range fills every cell in that range with that value; it does not distribute different values automatically.

Resize, Offset, and dynamic ranges

When the target area changes size, methods such as Resize and Offset help build a range from a starting point. Resize(rowSize, columnSize) returns an area with the specified number of rows and columns. Offset(rowOffset, columnOffset) returns an area moved from the original range. These methods return Range objects; they do not change the original range’s address.

Sub BuildBlock()
    Dim ws As Worksheet
    Dim block As Range

    ' Create a five-row by three-column block starting at A1.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set block = ws.Range("A1").Resize(5, 3)
    block.Font.Bold = True
End Sub

That example formats A1:C5. To find the last used row in a particular data column, a common pattern is ws.Cells(ws.Rows.Count, "A").End(xlUp).Row. It starts at the bottom of column A and moves upward to the next non-empty cell. If the column has a heading in row 1 but no data, the result is still 1, so code should account for an empty-data case when that distinction matters.

“Used range” and “last row of data” are not always the same. A worksheet’s used area can extend because cells were previously formatted or edited, even when they look blank now. For a dependable last-row check, choose a column that should contain a value for every relevant record, and decide how to handle blanks within that column.

Loop through rows without selecting cells

A macro usually does not need to select a cell before working with it. Directly referencing the range is more reliable and avoids moving the user’s selection around. This example checks each nonblank entry in column A and writes a status in column B.

Sub MarkEntries()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim r As Long

    ' Find the final non-empty cell in column A.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For r = 2 To lastRow
        If Len(ws.Cells(r, "A").Value2) > 0 Then
            ws.Cells(r, "B").Value = "Checked"
        End If
    Next r
End Sub

The loop starts at row 2 because row 1 is assumed to contain headings. It writes “Checked” for each non-empty value it encounters in A2 through the last detected row. If column A is empty below the heading, the loop does not run because lastRow is 1. Change the assumed columns and starting row to match your worksheet layout.

For large datasets, repeatedly accessing individual cells can take longer than transferring values in one array operation. For small tables, a straightforward loop is often easier to understand. Choose the simplest approach that fits the size and needs of the workbook.

Named ranges and multiple areas

A range can refer to a defined name rather than a cell address. For example, if a workbook contains a named range called SalesTotal, VBA can refer to it through the relevant worksheet or workbook scope. Names are useful when a target has a meaningful role or may move as a worksheet is reorganized. Be aware that Excel names can have workbook scope or worksheet scope, and a worksheet-level name can take precedence in its local context.

A range can also contain more than one separate area. In Excel, selecting A1:A3 and C1:C3 creates a multi-area selection. Such a range is less convenient for some operations than one contiguous rectangle; not every property or method behaves as if the areas were a single block. If code must handle separate areas, inspect the range’s Areas collection and process each area deliberately.

When you need to combine ranges in VBA, the Union method can create a multi-area range, while Intersect returns the overlap between ranges on the same worksheet. These are useful for conditional tasks, but they do not merge the cells or move data. They create references to existing cells.

Common Range mistakes and how to avoid them

  • Relying on the active sheet: Range("A1") can point to a different worksheet than expected. Qualify it with a worksheet variable.
  • Confusing row and column order: Cells(row, column) takes the row first. Thus, Cells(2, 3) is C2, not B3.
  • Overwriting formulas: Assigning to Value replaces cell contents. Confirm that the target is meant to receive a value before writing.
  • Assuming blank-looking cells are empty: A cell can contain a formula returning an empty string, spaces, or formatting. Decide whether your test should check the stored value, a formula, or other cell properties.
  • Using an unreliable last-row column: If the chosen column has gaps or may be blank, the last-row method can stop too early. Select a consistently populated column or use a more deliberate search.
  • Mixing workbooks: ThisWorkbook and ActiveWorkbook may refer to different files. Qualify both the workbook and worksheet when a macro works across multiple files.

When debugging, inspect the exact worksheet and address before changing data. The VBA editor’s Locals and Immediate windows can help examine variables while code is paused. A simple habit is to assign the target to a named Range variable, then use that variable consistently rather than repeating ambiguous references.

Excel versions, platforms, and macro safety

VBA is available in the desktop versions of Excel for Windows and Mac, although some features and interface details differ. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros. A workbook with macros generally needs the .xlsm file format to retain its VBA project; saving it as a macro-free format can remove that project.

On Windows, Alt+F11 opens the Visual Basic Editor in Excel. On Mac, Option+F11 is the usual equivalent, though some keyboards require Fn+Option+F11. Keyboard settings can affect the shortcut. Excel’s Developer tab can be enabled in ribbon settings if it is not visible. Menu and settings labels can differ slightly between Mac releases.

Macros can alter or delete workbook data, and the normal Undo history does not restore changes made by VBA. Save a separate copy before running unfamiliar code, and only enable macros from sources you trust. A macro that writes to a range should be reviewed for its workbook, worksheet, and address references before execution.

Frequently Asked Questions

What does Range mean in Excel VBA?
A Range is an object reference to one or more worksheet cells. VBA uses it to read or change values, formulas, formatting, and other cell properties.

What is the difference between Range and Cells in VBA?
Range commonly uses an address such as "A1:B5", while Cells(row, column) uses numeric row and column positions. Both return references to worksheet cells, and both should be qualified with the intended worksheet.

How do I refer to a cell on a specific sheet?
Use a worksheet-qualified expression such as ThisWorkbook.Worksheets("Sheet1").Range("A1"). This avoids relying on whichever worksheet happens to be active.

Can Excel for the web run VBA?
No. Excel for the web can display and edit workbook data, but VBA macros must be run in desktop Excel for Windows or Mac.

Can I undo a VBA macro with Ctrl+Z?
Usually not. Running VBA clears or bypasses Excel’s usual undo history, so save a copy before a macro makes changes.

Related

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.