- Computers & Software»
- Computer Software
Use Offset to move around an Excel Spreadsheet
There are many ways to move around a worksheet in Excel. Using Excel VBA the Offset property enables you to move around a worksheet a given number of rows and columns.
It is commonly found in loops to move down a row or across a column each cycle. It can be used to move in different directions depending on a value in conditional loops.
Using Offset with the ActiveCell object enables you to move in relation to your current position. This is an extremely useful and popular requirement in Excel.
The Offset Syntax
The Offset property can be used to move positively (down and right) in a worksheet, or negatively (up and left). This is done by entering positive or negative values as the Offset arguments.
Offset(Row Offset, Column Offset)
For example, to move down a row you would enter.
Or to move up a row and right across 2 columns you would enter.
Notice the negative value in the row argument and the positive value for the column offset.
Selecting Ranges with Offset
Use the Offset property of the Range object to select ranges in a worksheet. The statement below would select a range 5 rows high and 5 columns wide using the selected cell in the top left corner.
Want to copy the range of cells. Use the Copy method. There is no need to select the range first.