Using the Offset Property

Another very flexible way to refer to a worksheet cell is with the Offset property. When automating worksheet tasks, you may not know exactly where a specific cell is located. How can you select a cell whose address you don't know? You can have Excel select a cell based on an existing selection. The Offset property calculates a new range by shifting the starting selection down or up a specified number of rows. You can also shift the selection to the right or left a specified number of columns. In calculating the position of a new range, the Offset property uses two arguments. The first argument indicates the row offset, and the second one is the column offset. Let's try out some examples.

To render this into VBA: Enter this in the Immediate window:

Select a cell located one row down and Range("A1").Offset(1, 3).Select three columns to the right of cell A1.

Select a cell located two rows above and Range("D15").Offset(-2, -1).Select one column to the left of cell D15.

Select a cell located one row above the ActiveCell.Offset(-1, 0).Select active cell.

In the first example above, Excel selects cell D2. As soon as you enter the second example, Excel chooses cell C13.

If cells A1 and D15 are already selected, you can rewrite the two above examples in the following way:

Selection.Offset(1, 3).Select Selection.Offset(-2, -1).Select

Notice that the third example in the practice table above displays zero (0) in the position of the second argument. Zero entered as a first or second argument of the Offset property indicates the current row or column. The instruction ActiveCell.Offset(-1, 0).Select will cause an error if the active cell is located in the first row.

When working with the Offset property, you may occasionally need to change the size of a selection of cells. Suppose that the starting selection is A5:A10. How about shifting the selection two rows down and two columns to the right and then changing the size of the new selection? Let's say the new selection should highlight cells C7:C8. The Offset property can only take care of the first part of this task. The second part requires another property. Excel has a special Resize property. You can combine the Offset property with the Resize property to answer the above question. Before you combine these two properties, let's see how you can use them separately:

1. Arrange the screen so that the Microsoft Excel window and the Visual Basic window are side by side.

2. Activate the Immediate window and enter the following instructions:

Range("A5:A10").Select Selection.Offset(2, 2).Select Selection.Resize(2, 4).Select

The first instruction above selects the range A5:A10. Cell A5 is the active cell. The second instruction shifts the current selection to cells C7:C12. Cell C7 is located two rows below the active cell A5 and two columns to the right of A5. Now the active cell is C7. The last instruction resizes the current selection. Instead of range C7:C12, cells C7:C8 are selected. Like the Offset property, the Resize property takes two arguments. The first argument is the number of rows you intend to include in the selection, and the second argument specifies the number of columns. Hence, the instruction Selection.Resize(2, 4).Select resizes the current selection to two rows and four columns.

0 0

Post a comment