Excel VBA OFFSET Property for Range

What is Excel VBA OFFSET Property?

Excel VBA OFFSET property returns the Range object which is number of rows and columns away from a specific range. You can specify to shift up, down, left or right from a cell.

For example, if column A contains employee ID, and column B contains employee name, you can find the employee name in B2 by shifting A2 to the right. (shift 1 column to the right and shift 0 row). The advantage of using OFFSET property is that even when the actual column change, you don’t need to hardcode “column B”, you just need to specify the column which is one column right from the employee ID column.

Note that Excel VBA OFFSET is a “Property” for use in Excel VBA, it is accessed through an object such as Range. For example, Range(“A2”).OFFSET(row, column)

There is also OFFSET “Function” for worksheet, but the parameters are slightly different (without height and width). Read the below article for OFFSET Function.

Excel VBA OFFSET function

Syntax of OFFSET Propety

OFFSET( reference, rows, columns)

reference is the specified range where the shift starts

rows is the number of rows to shift from the specified range; positive number indicates right shift, negative number indicates left shift

columns is the number of rows to shift from the specified range; positive number indicates down shift, negative number indicates up shift

Formula Return Explanation
Range(“A1”).OFFSET(1,0) range A2 Shift 1 row down from A1
Range(“A1”).OFFSET(A1,1,2) range C2 Shift 1 row down from A1
then shift 2 columns right

Outbound Reference

http://msdn.microsoft.com/en-us/library/office/aa224911%28v=office.11%29.aspx

http://www.excel-easy.com/vba/examples/offset.html

http://www.homeandlearn.org/the_offset_property.html

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *