Use the Offset Property in VBA

Excel VBA offers many different ways to refer to ranges in a worksheet. One very popular way is to use the Offset property of the Range object.

The Offset property allows you to move around a worksheet with ease using ranges relative to your current position, or to a specific range.

The Offset property takes two arguments. These relate to the upper left-hand corner of the specified Range object. The first argument is the number of rows to offset, and the second is the number of columns.

The arguments can be positive to move down and to the right, or negative to move up and to the left. For example, the following selects cell C3.

Range(“C2”).Offset(1,0).Select

The Offset property is probably more commonly used with the ActiveCell to move relative to the currently selected cell. The following example is a little more complex. It selects the cell one row above and two columns to the right of the current cell.

ActiveCell.Offset(-1,2).Select

If the currently selected cell was C2, then cell E1 would be selected. If cell C1 was the selected cell then an error message would be returned as it cannot find a Range object that does not exist (there are no rows above row 1).

The example below shows the Offset property being used to select a range of cells 4 rows high and 4 columns wide from the current cell.

Range(ActiveCell,ActiveCell.Offset(4,4)).Select

The Offset property is extremely useful when used in loops and when working with variables. The example below demonstrates a Do While loop being used to enter the numbers 1 to 20 in a column starting from the active cell.

Dim x As Integer
x = 1
Do While x <= 20
   ActiveCell.Value = x
   ActiveCell.Offset(1, 0).Select
   x = x + 1
Loop

As you can see the Offset property can be used in a variety of scenarios. It is the most popular method of moving relatively around a worksheet. In fact if you ever record a macro in Excel with the relative references command turned on, the Offset property is used.

This post serves as an introduction to its syntax and use. Go forth and experiment with some macros.