VBA Excel Offset: How to Select a Range Reliably

VBA & macros · Updated November 21, 2026

In Excel VBA, use the Offset property on a Range to refer to cells a chosen number of rows and columns away: for example, Range("B2").Offset(1, 2) refers to D3. To select that range, use .Select after making its worksheet active, though assigning the range to a variable is usually safer than selecting it.

What Range.Offset means in VBA

Offset is a property of a VBA Range object. It returns another range positioned relative to the original one; it does not move cells, copy data, or change the original range. Its arguments are the number of rows to move and then the number of columns to move. Positive values move down or right, negative values move up or left, and zero means stay on that axis.

For example, starting from B2, an offset of one row down and two columns right points to D3. Starting from B2, an offset of minus one row and zero columns points to B1. The reference is a range object, so you can read or write its values, format it, or use methods such as Select when selection is genuinely needed.

The worksheet function OFFSET is a related but different feature. It is entered in a cell as a formula and returns a reference for a worksheet calculation. VBA’s Range.Offset is used in macro code. The shared name can be confusing, but the syntax and purpose differ.

Basic syntax and selecting a cell

The general form is someRange.Offset(RowOffset, ColumnOffset). Both arguments are optional and default to zero, so Range("B2").Offset(1) moves one row down while remaining in column B. It is clearer to provide both numbers in examples and in code that others may maintain.

Here is a complete macro that selects D3, using B2 as its starting point. It explicitly qualifies the worksheet and activates it before selecting, because Excel can select a range only on the active sheet.

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

    Set ws = ThisWorkbook.Worksheets("Data")
    Set target = ws.Range("B2").Offset(1, 2)

    ws.Activate
    target.Select
End Sub

The code assumes the workbook contains a worksheet named Data. Change that name to match the workbook. In the Visual Basic Editor on Windows, press Alt+F11 to open it; on Mac, Option+F11 may work, or Fn+Option+F11 depending on keyboard settings. Before running a macro, save a copy of the workbook: VBA changes cannot be undone with Ctrl+Z (or Command+Z on Mac).

Offsetting a range instead of one cell

Offset preserves the dimensions of the range it starts from. If the starting range is three rows tall and two columns wide, the returned range is still three rows tall and two columns wide, just shifted. This lets you move a block without recalculating its size.

For instance, the following macro starts with B2:C4, a three-by-two block, and targets D3:E5 by moving one row down and two columns right. It selects the shifted block only after activating the correct worksheet.

Sub SelectOffsetBlock()
    Dim ws As Worksheet
    Dim sourceBlock As Range
    Dim targetBlock As Range

    Set ws = ThisWorkbook.Worksheets("Data")
    Set sourceBlock = ws.Range("B2:C4")
    Set targetBlock = sourceBlock.Offset(1, 2)

    ws.Activate
    targetBlock.Select
End Sub

Offset can also be chained with another range property or method. For example, a macro can refer to a shifted range’s value without selecting it, or apply formatting directly to that range. The key is to keep a clear reference to the worksheet and the intended range rather than relying on whichever cell happens to be active.

Use Resize to change the dimensions

A common misconception is that Offset also changes the size of a range. It does not: it shifts the range while retaining its current row and column count. Use the Resize property when you need a different number of rows or columns. You can combine the two: offset to the desired starting location, then resize to the required dimensions.

This example uses B2 as an anchor, moves one row down and two columns right, then creates a two-row by three-column target, D3:F4:

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

    Set ws = ThisWorkbook.Worksheets("Data")
    Set target = ws.Range("B2").Offset(1, 2).Resize(2, 3)

    ws.Activate
    target.Select
End Sub

Resize arguments are the new total row and column counts, not the amount to add. Thus Resize(2, 3) makes the range two rows tall and three columns wide. When building a target from a single-cell anchor, this distinction makes the final boundaries easier to verify.

How to choose a reliable starting point

Offset only makes sense relative to a starting range. A hard-coded anchor such as B2 is appropriate when the sheet layout is fixed. For a changing data table, derive the anchor from a known header, named range, or other stable location so that adding rows or columns does not silently shift the macro’s meaning.

  • For a fixed template, use an explicitly qualified reference such as ws.Range("B2").
  • For a table that grows, consider referencing its header or a cell located from the table object, then offset from that meaningful reference.
  • For the cell next to the current active cell, use ActiveCell.Offset(0, 1) only if the macro is intentionally dependent on the user’s selection.
  • For a calculated output range, assign the range to a variable and work with that object, rather than selecting it first.

Avoid unqualified references such as Range("B2").Offset(1, 2) in code that may run while another workbook or worksheet is active. VBA will resolve an unqualified Range against the active sheet, which may not be the sheet the macro author intended. Qualifying it as ws.Range("B2") makes the target explicit.

Selecting versus working directly with a range

Selection is visible and useful when a macro should leave the user at a particular location, but it creates a dependency on Excel’s active workbook and sheet. Most operations do not require selection. For instance, to put a value in the cell one row below B2 and two columns to the right, address the returned range directly:

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

    Set ws = ThisWorkbook.Worksheets("Data")
    Set target = ws.Range("B2").Offset(1, 2)
    target.Value = "Complete"
End Sub

This writes Complete to D3 without activating the worksheet or changing the user’s selection. The same pattern works for reading a value, applying number formats, or clearing contents. In larger macros, direct range references are generally easier to debug because each operation states exactly which object it affects.

If the task really is to select a range, first ensure the target worksheet is active. A target range on an inactive worksheet cannot be selected, and attempting it can trigger a runtime error. This is one reason that selecting a range is not a good substitute for properly qualifying references.

Common errors and boundary checks

An offset can point outside the worksheet grid. For example, moving above row 1 or left of column A produces an invalid reference and can raise an error. Excel worksheets have fixed row and column limits, so a large positive offset can also exceed the available grid. If the offset depends on user input or calculated values, validate it before using it.

Other frequent problems include:

  • Wrong active sheet: selecting a range on a different sheet fails. Qualify the source with a worksheet variable and activate that sheet only if selection is required.
  • Unexpected start cell: code using ActiveCell changes its result according to the current selection. Use a known anchor when the target must be predictable.
  • Wrong range size: Offset keeps the original dimensions. Add Resize if the target must have a different shape.
  • Confusing row and column order: the first argument is rows, the second is columns. Offset(0, 1) goes right, not down.
  • Missing worksheet qualification: a bare Range reference can point to a different sheet than expected if the active sheet changes.

When diagnosing a bad result, inspect the anchor first, then the row and column offsets, and finally the dimensions. Temporarily assigning the result to a Range variable and checking its address in the debugger can help reveal which cell or block the expression resolves to. Do not assume the word Select is required just because you want to identify the target.

Frequently Asked Questions

How do I move one cell to the right in VBA?
Starting from a range, use Offset(0, 1). For example, ws.Range("B2").Offset(0, 1) refers to C2. The first argument is the row movement and the second is the column movement.

How do I select a cell with Offset?
Set a Range variable to the offset reference, activate its worksheet, then call Select on the variable. The worksheet must be active because Excel does not allow selecting a range on a different sheet.

Does Offset change the size of a range?
No. It moves a range and preserves its existing row and column dimensions. Combine it with Resize to create a target with a different size.

Can I use a negative Offset?
Yes. A negative row value moves up, and a negative column value moves left. The resulting reference must still remain inside the worksheet grid.

What is the difference between VBA Offset and the Excel OFFSET function?
VBA’s Range.Offset is an object property used in macro code. The worksheet OFFSET function is a formula entered in a cell. They share a name but are used in different contexts.

Practical takeaway

Use Range.Offset(rowCount, columnCount) to calculate a relative cell or block, remembering that rows come first and that the original range’s size is preserved. Add Resize when the dimensions need to change. For dependable macros, qualify the starting range with its worksheet and work directly with the returned Range object; activate and select it only when showing a selection to the user is an actual requirement.

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.