How to use VBA Range.Offset Method?
Range.Offset is a property of the VBA range object that is used when you want to point the cell selection to a specific column and row address. For an instance, if you want to skip some information like row header or document title.
Syntax
expression .Offset(RowOffset, ColumnOffset)
expression A variable that represents a Range object.
Parameters
| Name | Required/Optional | Data Type | Description | ||
| RowOffset | Optional | Variant |
|
||
| ColumnOffset | Optional | Variant | The number of columns (positive, negative, or 0 (zero)) by which the range is to be offset. Positive values are offset to the right, and negative values are offset to the left. The default value is 0. |
Below are the sample codes:
- To activate the cell five columns to the right and five rows down of the selected cell in the worksheet.
Worksheets("Sample").Activate
ActiveCell.Offset(rowOffset:=5, columnOffset:=5).Activate
- This example will select the table without selecting the header row. Selected cell should be somewhere in the table.
Set tbl = ActiveCell.CurrentRegion tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _  tbl.Columns.Count).Select
3. The following sample will select the range 4 rows below and 3 columns to the right of Range(“B1:B2”). Offset property always takes the top left cell of a range as the starting point.
Dim OffsetSample As Range
Set OffsetSample = Range("B1:B2")
OffsetSample.Offset(4, 3).Select
