In Excel VBA, Offset refers to a cell or range a chosen number of rows and columns away from another range. For example, Range("B2").Offset(1, 2) refers to D3: the reference moves one row down and two columns right, without changing or moving the worksheet’s cells.
What Offset means in VBA
Offset is a property of Excel’s Range object. It creates a reference relative to the range before the dot. It does not copy, insert, or shift cells; it simply tells VBA which cells to work with. That distinction matters: assigning a value through an offset reference changes the destination cell, but using Offset by itself does not change the workbook.
The arguments describe movement from the starting range, not a worksheet address. The first argument is the row displacement, and the second is the column displacement. Positive numbers move down or right; negative numbers move up or left. A zero means stay in the same row or column.
Range("B2").Offset(1, 0)refers to B3.Range("B2").Offset(0, -1)refers to A2.Range("B2").Offset(-1, 2)refers to D1.Range("B2").Offset(0, 0)refers to B2 itself.
Syntax and arguments
The general form is expression.Offset(RowOffset, ColumnOffset), where expression is a range. Both arguments are optional; if one is omitted, its displacement is zero. For clarity, especially when code is maintained by someone else, it is usually helpful to supply both arguments.
For example, if the starting cell is B2, an offset of 1 row and 2 columns lands on D3. Excel counts columns from the starting point: B to C is one column, then C to D is two. It is not the absolute column number of the destination.
VBA also allows named arguments, which can make a less familiar call easier to read: Range("B2").Offset(RowOffset:=1, ColumnOffset:=2). The result is still the range D3. You can use the returned range with properties such as .Value, .Address, or .Interior, or assign it to a Range variable.
Basic examples that read and write cells
To write to a cell next to a known starting cell, qualify the starting range with the worksheet you intend to use. In this example, with a worksheet named Sheet1 in the workbook containing the macro, the code writes “Checked” in C2, one column to the right of B2:
Sub WriteNextToCell()
Dim ws As Worksheet
' Use the worksheet that contains the target cell.
Set ws = ThisWorkbook.Worksheets("Sheet1")
ws.Range("B2").Offset(0, 1).Value = "Checked"
End Sub
The same idea works for reading a nearby cell. If B2 contains a product name and C2 contains its price, ws.Range("B2").Offset(0, 1).Value reads the value from C2. You can put that value into a variable, compare it, or use it in another calculation. The offset reference can also be used as the starting point for formatting, such as applying a number format to the cell beside a label.
Before running a macro that changes a workbook, save a copy. Excel macros cannot be undone with Ctrl+Z. Save a macro-enabled workbook as an .xlsm file if you need to retain VBA code.
Using Offset with a multi-cell range
When the starting reference contains several cells, Offset moves the whole range and preserves its dimensions. For example, a reference to B2:B5 offset by one row becomes B3:B6. It does not collapse the range to one cell. This is useful when a procedure needs to work on a block that sits next to or below another block.
Use Resize if you also need to change the height or width. For example, Range("B2").Offset(1, 0).Resize(3, 2) refers to B3:C5: it first moves the starting cell to B3, then creates a range three rows high and two columns wide. In VBA, the order matters because Offset moves the reference and Resize changes its dimensions.
Offset in loops and changing rows
A common reason to use Offset is to write a result beside each item in a list. A loop can move down a column and use a same-row offset for the output. The following example checks for the last used row in column A and copies each nonblank value into the adjacent cell in column B:
Sub CopyItemsToNextColumn()
Dim ws As Worksheet
Dim lastRow As Long
Dim rowNum As Long
' Identify the source worksheet and its last used row in column A.
Set ws = ThisWorkbook.Worksheets("Sheet1")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Copy each source value one column to the right.
For rowNum = 2 To lastRow
If ws.Cells(rowNum, "A").Value <> "" Then
ws.Cells(rowNum, "A").Offset(0, 1).Value = _
ws.Cells(rowNum, "A").Value
End If
Next rowNum
End Sub
The code assumes row 1 is a header and the list starts at A2. If column A has no data below its header, the loop does not run. If your data begins on a different row or the source column may contain gaps at the bottom, adjust the last-row logic to fit the layout. For a larger task, reading and writing whole arrays can be faster than accessing cells repeatedly, but Offset is often easy to understand for modest ranges.
Offset compared with Cells and direct addresses
Offset is convenient when the destination is defined by its relationship to another cell. For example, “the cell one column to the right of this label” remains meaningful even if the label’s row changes. By contrast, Range("C2") names a fixed address. A direct address can be clearer when the destination is known and never depends on another range.
The Cells(row, column) property uses row and column numbers to identify a cell on a worksheet. It is often a good choice in loops where the row number is already available. For example, ws.Cells(rowNum, 2) refers to column B in the row represented by rowNum. Use Offset when you want to express movement from an existing reference; use Cells when calculating an absolute row and column is more natural. Either approach can be clear and reliable when the worksheet is explicitly qualified.
These are VBA expressions, not worksheet formulas. Typing Offset into a worksheet cell is not how you use the VBA property. Excel has a separate worksheet function called OFFSET, but it has different syntax and behavior as a formula.
Common errors and safer practices
A frequent source of bugs is an unqualified reference such as Range("B2").Offset(0, 1). Without a worksheet qualifier, VBA uses the active sheet, which may not be the sheet the macro’s author intended. Likewise, ActiveCell.Offset(1, 0) depends on the current selection. Select a different cell and the destination changes.
For code that must target a particular sheet, use a worksheet variable and qualify the reference, as in ws.Range("B2").Offset(1, 0). This also makes it easier to see which sheet is involved. Avoid selecting cells just to read or write values; direct references are generally simpler and less sensitive to what is currently active.
An offset can also point outside the worksheet. Excel worksheets have a finite number of rows and columns, so moving above the first row, left of column A, below the last row, or beyond the last column can cause a run-time error. Check the starting row and column before applying a displacement that might cross an edge. A multi-cell range needs extra care because every cell in the moved range must still fit on the sheet.
- Confirm the starting range is on the intended worksheet.
- Check the sign and order of the row and column arguments.
- Remember that an offset moves a range; it does not insert or shift worksheet cells.
- Use
Resizeseparately if the range’s dimensions need to change. - Save a backup before running code that writes, clears, or formats cells.
Running VBA in Excel for Windows and Mac
In Excel for Microsoft 365 on Windows, open the VBA editor with Alt+F11, or choose Developer > Visual Basic. To run a macro, choose Developer > Macros, select the procedure, and choose Run. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon and select Developer.
Excel for Mac supports VBA, though some Windows-specific automation features and references are not available in the same way. The VBA editor shortcut is Option+F11 on many Mac keyboards; some keyboards require Fn as well. You can also open the editor from the Developer tab. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros. Macro security settings may prevent code from running, so only enable macros in files you trust.
Frequently Asked Questions
What does Offset(1, 0) mean in Excel VBA?
It refers to the range one row below the starting range, in the same column. For example, if the starting reference is B2, Range("B2").Offset(1, 0) refers to B3.
How do I move one cell to the left with Offset?
Use a row displacement of zero and a column displacement of negative one. From B2, Range("B2").Offset(0, -1) refers to A2.
Does Offset change or move the original cells?
No. Offset returns a reference to a different location. It does not move the original data or shift cells. If you assign a value through the returned reference, that assignment changes the destination cell.
Can Offset refer to a range instead of one cell?
Yes. If the starting reference contains multiple cells, the offset range retains the same number of rows and columns. Use Resize as well if you need to change the dimensions.
Why does my Offset macro act on the wrong worksheet?
An unqualified Range or a reference to ActiveCell can depend on the active worksheet or selection. Qualify the range with a worksheet object, such as ws.Range("B2").Offset(0, 1), to make the target explicit.
Related
- Text to Excel Converter: Import Text Into Columns
- Excel SUM and Hidden Cells: What Gets Included in Totals?
- Excel SUM and Multiply: How to Combine Them
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Loop Through a PivotTable: Examples and TipsTo loop through a PivotTable in Excel VBA, assign it to a PivotTable variable and use a For…
- Excel VBA Delete Worksheet: Code, Safety, and OptionsTo delete a worksheet with Excel VBA, call that worksheet’s Delete method. Excel normally displays a confirmation prompt;…
- VBA SortFields.Add2: What It Does and How to Use ItSortFields.Add2 is an Excel VBA method that adds a sort key to a sort operation. It works much…
- VBA AutoFilter Criteria1: Syntax, Examples & Tips in ExcelIn VBA, Criteria1 is the first filter condition passed to Excel’s AutoFilter method; for example, Criteria1:="East" keeps rows…
- VBA Dictionary Keys: How They Work and How to Use ThemIn VBA, a dictionary key is a unique identifier used to store and retrieve an associated value in…
- VBA AutoFilter Off: Remove Arrows or Clear FiltersTo show every row while keeping the filter controls, use VBA’s ShowAllData method when the worksheet is filtered.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.