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.
More by this Author
Although Excel’s greatest strength is its ability to analyse huge amounts of data, it is also great for creating forms. We can create formulas, apply validation rules and use techniques to make our forms user...
Form controls can be added to an Excel spreadsheet to create interactivity with the user. This article looks at using option button controls to allow a user to choose the data they want to see on a chart. The first...
This hub will explain how to create the scrolling text credits from the Star Wars movies in your PowerPoint presentation. To create the Star Wars credits we will use different animation effects simultaneously. ...
No comments yet.