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
The number of rows (positive, negative, or 0 (zero)) by which the range is to be offset. Positive values are offset downward, and negative values are offset upward. The default value is 0.
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:

  1. 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 

  1. 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 

Leave a Reply

Your email address will not be published. Required fields are marked *