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
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...
Microsoft Excel can do some amazing things. I like to attempt little projects testing what Excel can do. I was asked the other day to simulate a cup draw using Excel and this is the result. This article will give you...
Brock Lesnar is a phenomenon of a man and an athlete. Brock Lesnar weighs a lean 265 lbs and yet possesses extreme speed, agility and athleticism. How a man carrying so much muscle mass can maintain such high levels of...
No comments yet.