Using The Offset Property

■ y he Offset property provides another method for specifying a range of cells. Using the Offset property you can define a range that is a specific offset from another range with the offset being the distance, in rows and columns, between the new range and the existing range selection.

You use two different parameters with the Offset property. Although both are optional, you specify at least one of the values or the current selection is returned. Use the RowOffset parameter to indicate the number of rows to offset the range from the current selection. A positive number offsets the range downward. A negative value offsets the range upward. The offset values are based upon the the upper left cell in the selected range. For example, if the active range is cells A1 through B4, the offset values are based upon the number of rows and columns from cell A1.

Use the ColumnOffset parameter to specify the number of columns to offset the range from the current selection. A positive number offsets the range to the right. A negative number offsets the range to the left. The default value for both parameters is 0.

If you only assign a value to one of the parameters, Excel gives the other parameter a value of 0. For example, if you specify a value of 5 for the RowOffset and omit the ColumnOffset parameter value, the property returns the range that is five rows from the current range selection.

If you specify a value outside the valid number of rows and columns in a worksheet — for example, if you specify -1 and the current cell is A1 — Excel returns an error. The acceptable range for columns is 1 to 256 and the acceptable range for rows is 1 to 65536.

USING THE OFFSET PROPERTY

USING THE OFFSET PROPERTY

□ Create a new subrouti

< Type Dim NewRange As Type Set FirstRange =

Range, replacing NewRange Range("A1:B4"), replacing with the new range variable. Range("A1:B4") with the current range.

□ Create a new subrouti

Type Dim FirstRange As Range, replacing FirstRange with the current range variable.

< Type Dim NewRange As Type Set FirstRange =

Range, replacing NewRange Range("A1:B4"), replacing with the new range variable. Range("A1:B4") with the current range.

IEXJEE

0 0

Responses

  • randall
    How to use for next statement with offset property in VBA?
    11 months ago

Post a comment