In an Excel VBA macro, use the Offset property to refer to a cell or range a chosen number of rows and columns away from another range. For example, Range("A1").Offset(2, 1) refers to cell B3: two rows down and one column to the right.
What Offset means in Excel VBA
Offset is a property of a VBA Range object. It returns a different range relative to the starting range; it does not, by itself, move cells around on the worksheet or change the starting range. A macro can then read from or write to the returned range, format it, or use it in another operation.
The two arguments tell VBA how far to move:
- Row offset: a positive number moves down, and a negative number moves up.
- Column offset: a positive number moves right, and a negative number moves left.
A zero means stay in the same row or column. The arguments are optional, so an omitted argument is treated as zero. Thus, moving one column to the right without changing rows can be expressed as Offset(0, 1). The result is still a range reference: to change a cell’s contents, add an operation such as .Value = "Done".
Offset syntax and simple examples
The general pattern is startingRange.Offset(rowOffset, columnOffset). If the starting range is A1, Offset(1, 0) refers to A2, Offset(0, -1) would attempt to refer to the cell immediately left of A1, and Offset(-1, 0) would attempt to refer to the cell above it. Those last two references are outside the worksheet, so they cause an error. An offset must lead to a valid cell or range on the sheet.
Offset works with ranges containing more than one cell, too. If the starting range is B2:D4, Offset(1, 0) refers to B3:D5. The whole range shifts down one row and keeps its original three-row by three-column size. Offset changes the reference location, not the dimensions.
These examples are useful when translating a worksheet layout into VBA. Start with the known cell or block, then count the number of rows and columns to the target. Keeping the two directions separate helps avoid a common mistake: the first argument is rows, not columns.
Write to an adjacent cell with a macro
Suppose Sheet1 contains a value in A2 and the goal is to copy it into B2. With the source in A2, the destination is zero rows down and one column right. This example uses an explicit worksheet variable, which makes the target sheet clear and avoids relying on whichever sheet happens to be active.
Before running VBA that changes a workbook, save a separate copy: macro changes generally cannot be reversed with Ctrl+Z. With the source value in Sheet1!A2, the code below writes the same value to Sheet1!B2.
Sub CopyA2ToB2()
Dim ws As Worksheet
' Use the worksheet that contains the source and destination.
Set ws = ThisWorkbook.Worksheets("Sheet1")
ws.Range("A2").Offset(0, 1).Value = ws.Range("A2").Value
End Sub
Here, ThisWorkbook means the workbook containing the VBA code. If the code is stored in a personal macro workbook or another file, that may not be the workbook holding the data. In that situation, identify the intended workbook explicitly rather than assuming ThisWorkbook is correct. Also check that the worksheet tab is actually named Sheet1; VBA reports an error if it cannot find that sheet.
You could write directly to B2, but using Offset is valuable when the destination is defined in relation to a source cell and that relationship needs to be repeated for many rows.
Use Offset to process rows of data
Consider a table with names or record identifiers in column A, data beginning on row 2, and a status column in C. The following macro finds the last used row in column A and writes “Checked” in column C for each nonblank entry. From each cell in column A, the status cell is two columns to the right.
The example assumes column A contains ordinary values rather than Excel error values. It produces the word Checked in C2:C(last used row) wherever the corresponding A cell is not blank; blank A cells are left alone.
Sub MarkRowsChecked()
Dim ws As Worksheet
Dim lastRow As Long
Dim rowNum As Long
' Set the worksheet and find the final populated row in column A.
Set ws = ThisWorkbook.Worksheets("Sheet1")
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
' Start at row 2 to leave the header row unchanged.
For rowNum = 2 To lastRow
If ws.Cells(rowNum, 1).Value <> "" Then
ws.Cells(rowNum, 1).Offset(0, 2).Value = "Checked"
End If
Next rowNum
End Sub
The For loop changes the row number on each pass. The Offset moves across within that row, so A2 points to C2, A3 points to C3, and so on. You could instead refer directly to column C with ws.Cells(rowNum, 3). Offset makes the relative relationship visible; direct cell coordinates can be easier to read when the destination column is fixed and its position is not naturally relative to the source.
One limitation is that finding the last used row this way depends on column A being the column that defines the records. If column A has gaps near the bottom or another column is more reliable, choose the appropriate column for the last-row calculation. If there are no data rows below the header, the loop does not run because its start value is greater than its end value.
Move a range and change its size
Offset preserves the size of the original range. To refer to a range that has both moved and changed dimensions, combine Offset with the Resize property. For instance, starting at A1, moving down one row gives A2; resizing that one-cell range to three rows and two columns gives A2:B4.
This distinction matters when selecting a block for an operation such as formatting or clearing contents. Offset answers “where should the range begin relative to this reference?” Resize answers “how many rows and columns should the resulting range contain?” The methods can be chained because each returns a range reference.
For example, if a macro starts from a header cell in A1 and needs the first six data cells across columns A through F on row 2, it can first move down one row and then resize to one row by six columns. Make sure the resulting range fits on the worksheet and matches the intended data shape. A mistaken resize can affect more cells than expected, which is another reason to verify the target before running a macro that writes or clears data.
Offset versus Select, Cells, and direct references
Offset is often used with ActiveCell, such as ActiveCell.Offset(1, 0), to refer to the cell below the currently selected cell. This can be convenient for a macro intended to start from the user’s selection, but it also makes the result depend on where the user clicked. For repeatable work, an explicit reference such as ws.Range("A2") is usually easier to audit.
A macro does not need to select a cell before changing it. For example, assigning a value through ws.Range("A2").Offset(0, 1).Value acts on B2 without changing the user’s selection. Selecting ranges unnecessarily can make macros slower and introduce problems when the wrong worksheet is active.
Cells(row, column) is another way to refer to cells, especially inside loops. It is useful when row and column numbers are calculated. Offset is often clearer when the target is described as a fixed distance from a known range. Neither approach is always superior: pick the one that makes the intended target easiest to understand, and qualify references with the worksheet when possible.
Common Offset errors and how to prevent them
A frequent problem is reversing the arguments. In Offset(2, 1), VBA moves two rows and one column, not one row and two columns. A quick way to check is to say the arguments aloud in order: down or up first, then left or right.
- Subscript or range errors: an offset beyond the worksheet boundary cannot produce a valid range. Check the starting cell and the direction, especially when using negative values.
- Wrong sheet: an unqualified
Rangereference can act on the active sheet. Use a worksheet variable, as in the examples, when the macro must always target a particular sheet. - Unexpected active cell: code based on
ActiveCellchanges behavior depending on the selection. Use a fixed reference or deliberately validate the active selection. - Wrong range size: Offset moves a range but does not resize it. If the intended target has different dimensions, use Resize and confirm the final address before changing values.
- Overwriting existing data: a valid reference can still point to valuable content. Check the destination column and save a copy before running a write operation.
For troubleshooting, temporarily inspect the resulting address in the VBA editor’s Immediate window or assign the target to a Range variable and check its address in a controlled test. Do not treat a successful run as proof that the macro changed the intended cells; verify the output on the worksheet.
Run and save an Excel macro safely
In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, insert a standard module using Insert > Module, and paste the procedure there. To run a macro, use Developer > Macros, select its name, and choose Run. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon. The workbook must be saved in a macro-enabled format such as .xlsm to retain VBA code.
On Mac, use Developer > Visual Basic to open the VBA editor and Developer > Macros to run a macro; keyboard shortcuts may differ with the keyboard and system settings. Excel for the web can open and edit many workbook contents, but it does not run VBA macros. Use the desktop Excel application to execute this code.
Only enable macros in workbooks you trust. A macro can overwrite or clear data, and Excel may display a security warning depending on where the file came from and the organization’s security settings. Keep an untouched backup and test code on sample data before using it on an important workbook.
Frequently Asked Questions
What does Offset(1, 0) mean in Excel VBA?
It refers to a range one row below the starting range, in the same column. From A1, it points to A2. The first argument is the row movement and the second is the column movement.
Does Offset change the original cell?
No. Offset returns a reference to a different location; it does not move or edit the original cell. To change the destination, use a property such as .Value or apply another operation to the returned range.
How do I offset one column to the right?
Use a row offset of zero and a column offset of one: Offset(0, 1). From A2, that refers to B2. To move left one column, use Offset(0, -1), provided the resulting address remains on the worksheet.
Can I use Offset with a multi-cell range?
Yes. It shifts the entire range by the specified number of rows and columns while preserving its dimensions. Use Resize as well if the target range needs a different number of rows or columns.
Can an Excel macro use Offset without selecting cells?
Yes. You can assign values, read data, or format a range returned by Offset directly. Selecting is not required, and using explicit worksheet references can make a macro more predictable than relying on the active selection.
Related
- Excel Not Updating Links From Other Workbooks? Fixes
- Excel Not Keeping Formatting? Causes and Fixes
- Excel Not Blank Conditional Formatting: How to Set It Up
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Hex: Convert Numbers and ColorsIn Excel VBA, use the Hex function to turn a number into its hexadecimal text representation: for example,…
- VBA Sort Reference Is Not Valid: Causes and Fixes“The sort reference is not valid” is an Excel sorting error, not a VBA syntax rule. In a…
- 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…
- VBA Break Workbook Links: What the Macro Actually DoesYes. Excel VBA can break external workbook links: Excel replaces formulas that depend on linked workbooks with their…
- Excel Macro to Get Row Count: Choose the Right VBA MethodTo count data rows in an Excel macro, count a column that has one nonblank entry for every…
- Excel VBA: Find a Character Position in a String with InStrIn Excel VBA, use InStr to find where a character or text fragment first appears in a string:…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.