This Excel VBA tutorial explains how to use Excel VBA OFFSET property, and explain difference between VBA OFFSET Property and worksheet OFFSET Function.
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.
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
Example of OFFSET Propety
| 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 |
