In Excel VBA, use ActiveCell.Offset(rowOffset, columnOffset).Select to select a cell relative to the active cell; use Resize after Offset when you need a range. For example, ActiveCell.Offset(1, 0).Select selects the cell one row below the active cell.
How Offset works in VBA
Offset is a property of a Range object. It returns another range shifted from the original one by a specified number of rows and columns. The first argument is the row difference; the second is the column difference. Positive values move down or right, negative values move up or left, and zero leaves that dimension unchanged.
For example, if the active cell is D5, ActiveCell.Offset(2, 1) refers to E7: two rows down and one column to the right. The cell is not selected just because the expression identifies it. Add .Select when selection is specifically required. You can also assign the returned range to a variable to inspect or work with it without changing the selection.
ActiveCell.Offset(0, 1)refers to the cell one column right.ActiveCell.Offset(-1, 0)refers to the cell one row up.ActiveCell.Offset(3, -2)refers to a cell three rows down and two columns left.ActiveCell.Offset(0, 0)refers to the active cell itself.
Offsets count worksheet rows and columns, not visible rows and columns. If rows or columns are hidden, an offset still moves by the specified number of worksheet positions. Filtering also does not make an offset skip hidden records.
Select one cell relative to ActiveCell
For a basic macro, place the cursor in the cell that should serve as the starting point, then run code like this. The example selects the cell directly below it:
Sub SelectCellBelowActiveCell()
' Select the cell one row below the current active cell.
ActiveCell.Offset(1, 0).Select
End Sub
To move in another direction, change the two numbers. For example, use ActiveCell.Offset(0, -1).Select to select the cell immediately to the left, or ActiveCell.Offset(-2, 0).Select to move up two rows. The starting point is whichever cell is active when the macro runs, so the same macro can be reused from different locations.
In Microsoft 365 for Windows, open the VBA editor from the Developer tab by choosing Developer > Visual Basic. Insert a standard module, paste the macro, and run it while the intended starting cell is active. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon and select Developer. The VBA editor and macros are also available in desktop Excel for Mac, though ribbon and editor details can differ. Excel for the web cannot run VBA macros.
Select a multi-cell range with Resize
Offset keeps the dimensions of the range it starts from. Since ActiveCell is one cell, an offset from it is also one cell. To select a block, use Resize(rowSize, columnSize) to specify the new height and width. This example starts one row below the active cell and selects a block three rows high and two columns wide:
Sub SelectBlockBelowActiveCell()
Dim target As Range
' Offset the starting cell, then set the block dimensions.
Set target = ActiveCell.Offset(1, 0).Resize(3, 2)
target.Select
End Sub
If the active cell is D5, the example selects D6:E8. The offset determines the block’s top-left cell; the two Resize arguments determine its number of rows and columns. To select a 1-by-5 strip beginning two columns to the right, use ActiveCell.Offset(0, 2).Resize(1, 5). Use positive sizes for ordinary ranges; zero-sized or negative dimensions do not describe a valid range to select.
You can also offset an existing multi-cell range. For example, if baseRange refers to a 2-by-3 block, baseRange.Offset(1, 0) returns another 2-by-3 block one row lower. It does not return only the cell at the new top-left position. This detail often explains why an offset selection is larger than expected when the starting object is a range rather than a single cell.
Choose between Select and a range variable
Selection is useful when a macro needs to leave a particular area highlighted for the user, or when an older procedure depends on the current selection. For most automation, however, it is simpler and more reliable to refer to the target range directly. Selecting cells changes the interface state, can move the user’s view, and may fail if the intended worksheet is not active. A range variable lets code act on a target without those side effects.
For example, to clear contents in the block below the active cell without selecting it, assign the range to a variable and call ClearContents on that variable:
Sub ClearBlockBelowActiveCell()
Dim target As Range
' Identify a three-row, two-column block below the active cell.
Set target = ActiveCell.Offset(1, 0).Resize(3, 2)
target.ClearContents
End Sub
This clears cell contents but does not remove formatting. Because the macro changes workbook data, save a copy of the workbook before running it: macro actions cannot be undone with Ctrl+Z. If a task can be performed with a direct range reference, avoid selecting first and then acting on Selection; explicitly naming the target makes the code easier to understand and less dependent on what the user clicked.
Qualify the worksheet when the target is specific
ActiveCell means the selected cell on the active worksheet in the active workbook. That is convenient for a macro intended to work wherever the user starts, but it is not a fixed reference. If a macro must always work on a particular sheet, qualify the worksheet and set a range from that sheet instead. The example below targets the cell one row below A1 on a sheet named Data:
Sub SelectCellBelowDataA1()
Dim ws As Worksheet
Dim target As Range
' Use an explicit worksheet and starting cell.
Set ws = ThisWorkbook.Worksheets("Data")
Set target = ws.Range("A1").Offset(1, 0)
ws.Activate
target.Select
End Sub
Replace Data with the actual worksheet name. This example activates the sheet because Excel generally requires a range to be on the active sheet before selecting it. If selection is unnecessary, remove the activation and selection lines and work directly with target. ThisWorkbook refers to the workbook containing the macro; ActiveWorkbook instead refers to the workbook currently in front, which may be different.
Common errors and boundary issues
An offset can point outside the worksheet. For example, moving one row up from row 1 or one column left from column A does not produce a valid worksheet cell. A macro that accepts arbitrary starting positions should check its boundaries before selecting or changing a range. The far edges of an Excel worksheet are row 1,048,576 and column XFD, so an offset or resized block must remain within those limits.
Other frequent causes of trouble include:
- Wrong starting cell: The macro uses the active cell at runtime, not necessarily the cell that was active when the code was written.
- Wrong argument order:
Offset(2, 1)means two rows and one column, not the reverse. - Unexpected range size: Offsetting a multi-cell range preserves its size; use
Resizeif you need different dimensions. - Inactive worksheet: A range on another sheet usually cannot be selected until that sheet is activated. Direct range operations avoid this requirement.
- Protected sheet or merged cells: Protection can prevent selection or editing, and merged cells can make the apparent cell layout differ from the range coordinates. Check the sheet setup if a selection behaves unexpectedly.
Also distinguish the VBA Offset property from worksheet navigation. It does not depend on the current selection after the range object has been assigned, and it does not follow the next nonblank cell. It simply computes a position by row and column counts. For data navigation, such as finding the last used row, separate VBA methods may be more appropriate than a fixed offset.
Save and run macros safely
VBA code requires desktop Excel; it does not run in Excel for the web. If the workbook contains macros, save it in a macro-enabled format such as .xlsm, rather than relying on the standard .xlsx format to preserve VBA. Excel may display a security warning when opening a file with macros. Enable macros only for files you trust, and follow your organization’s security rules.
Before running a macro that changes cell contents, formats, or workbook structure, save a separate copy. Excel’s ordinary Undo command, Ctrl+Z on Windows, does not reverse VBA macro actions. A macro that only selects a range is not destructive, but it can still change the active cell and worksheet view, so confirm the intended starting cell before running it.
Frequently Asked Questions
How do I select the cell one row below the active cell in VBA?
Use ActiveCell.Offset(1, 0).Select. The first argument moves down one row and the second keeps the same column.
How do I offset left or right from ActiveCell?
Use ActiveCell.Offset(0, -1).Select for one cell left or ActiveCell.Offset(0, 1).Select for one cell right. Negative column offsets move left; positive ones move right.
How do I select a range instead of one cell?
Combine Offset with Resize. For example, ActiveCell.Offset(1, 0).Resize(3, 2).Select selects a range three rows high and two columns wide, beginning one row below the active cell.
Does Offset change the active cell automatically?
No. Offset returns a reference to a different range; it does not move the selection by itself. Add .Select to change the selection, or use the returned range directly in your code.
Can I use ActiveCell.Offset in Excel for the web?
Excel for the web does not run VBA macros. Use desktop Excel for Windows or Mac to run VBA; if the task must work in a browser, consider an alternative such as Office Scripts, which uses a different programming model.
Related
- Excel Text Number Format: How It Works and When to Use It
- Excel Text Filters: How to Find Matching Rows Easily
- Excel Text Box Wrap Text: How to Enable It
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- How to Put a Photo in a VBA UserFormTo put a photo in an Excel VBA UserForm, add an Image control, then either set its Picture…
- VBA Delete Whole Row: Safe Methods and ExamplesTo delete an entire worksheet row with VBA, use Rows(5).EntireRow.Delete, replacing 5 with the row number you want…
- Excel UserForm Grid Size: Spacing, Settings, and LayoutIn Excel VBA, a UserForm’s design grid is adjustable in the Visual Basic Editor; it does not have…
- Excel VBA: Insert the Current Date and Time in a CellTo insert a fixed date-and-time timestamp with Excel VBA, assign the VBA function Now to a cell, then…
- Excel Macro AutoFit Column Widths: VBA Code and TipsTo AutoFit column widths with an Excel macro, use VBA’s AutoFit method: select the cells or columns to…
- Excel VBA Copy Until Last Row: Reliable Code ExamplesTo copy a range in Excel VBA through the last populated row, find that row with End(xlUp), build…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.