Offset Property

Microsoft Excel Visual Basic

Show All

Offset Property

       

Offset property as it applies to the Range object.

Returns a Range object that represents a range that’s offset from the specified range. Read-only.

expression.Offset(RowOffset, ColumnOffset)

expression   Required. An expression that returns a Range object.

RowOffset  Optional Variant. The number of rows (positive, negative, or 0 (zero)) by which the range is to be offset. Positive values are offset downward, and negative values are offset upward. The default value is 0.

ColumnOffset  Optional Variant. The number of columns (positive, negative, or 0 (zero)) by which the range is to be offset. Positive values are offset to the right, and negative values are offset to the left. The default value is 0.

Offset property as it applies to the TickLabels object.

Returns or sets the distance between the levels of labels, and the distance between the first level and the axis line. The default distance is 100 percent, which represents the default spacing between the axis labels and the axis line. The value can be an integer percentage from 0 through 1000, relative to the axis label’s font size. Read/write Long.

expression.Offset

expression   Required. An expression that returns a TickLabels object.

Example

As it applies to the Range object.

This example activates the cell three columns to the right of and three rows down from the active cell on Sheet1.

Worksheets("Sheet1").Activate
ActiveCell.Offset(rowOffset:=3, columnOffset:=3).Activate

This example assumes that Sheet1 contains a table that has a header row. The example selects the table, without selecting the header row. The active cell must be somewhere in the table before the example is run.

Set tbl = ActiveCell.CurrentRegion
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _
    tbl.Columns.Count).Select

As it applies to the TickLabels object.

This example sets the label spacing of the value axis in Chart1 to twice the current setting, if the offset is less than 500.

With Charts("Chart1").Axes(xlValue).TickLabels
    If .Offset < 500 then
        .Offset = .Offset * 2
    End If
End With