VBA Range Offset

Home ➜ VBA Tutorial ➜ VBA Range Offset

Using OFFSET with the range object, you can navigate from one cell to another in the worksheet and you can also select a cell or a range. It also gives you access to the properties and methods that you have with the range object to use, but you need to specify the arguments in the OFFSET to use it.

Use OFFSET with the Range Object

1. Specify the range from where you want to start.
2. Enter a dot (.) to get a list of properties and methods.
3. Select the offset property and specify the arguments (row and column).
4. In the end, select property to use with the offset.

Select a Range using OFFSET

You can also select a range which is the number of rows and columns aways from a range. Take the below line of code, that selects a range of two cells which is five rows down and 3 columns right.

Range("A1:A2").Offset(3, 2).Select

Apart from that, you can also write code to select the range using a custom size. Take an example of the following code.

Range(Range("A1").Offset(1, 1), Range("A1").Offset(5, 2)).Select

To understand this code, you need to split it into three parts.

First thing first, in that range object, you have the option to specify the first cell and the last of the range.

Now let’s come back to the example:

• In the FIRST part, you have used the range object to refer to the cell that is one row down and one column right from the cell A1.
• In the SECOND part, you have used the range object to refer to the cell that us five rows down and two columns right from the cell A1.
• In the THRID part, you have used the cells from the part first and second to refer to a range and select it.

Using OFFSET with ActiveCell

You can also use the active cell instead of using a pre-defined range. That means you’ll get a dynamic offset to select a cell navigating from the active cell.

ActiveCell.Offset(5, 2).Select

The above line of code will select the cell which is five rows down and two columns right from the active cell.

Using OFFSET with ActiveCell to Select a Range

Use the following code to select a range from the active cell.

Range(ActiveCell.Offset(1, 1), ActiveCell.Offset(5, 2)).Select

To understand how this code works, make sure to see this explanation.

Copy a Range using OFFSET

Range(Range("A1").Offset(1, 1), Range("A1").Offset(5, 2)).Copy
Range(ActiveCell.Offset(1, 1), ActiveCell.Offset(5, 2)).Copy

Using Cells Property with OFFSET

You can also use the OFFSET property with the CELLS property. Consider the following code.

Cells(1, 3).Offset(2, 3).Select

The above code first refers to the cell A1 (as you have specified) with row one and column one using the cells property, and then uses the offset property to selects the cell which is two rows down and three columns.