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.

ActiveCell.Offset(1,0).Select

Or to move up a row and right across 2 columns you would enter.

ActiveCell.Offset(-1,2).Select

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.

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

Want to copy the range of cells. Use the Copy method. There is no need to select the range first.

Range(ActiveCell, ActiveCell.Offset(5,5)).Copy

More by this Author

  • Create an Interactive Excel Chart using Option Buttons
    6

    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...

  • Simulate a Cup Draw using Excel
    0

    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 Nutrition and Workouts
    2

    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.

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working