Subtotal Method

Applies To

Range Object.

Description

Creates subtotals for the range (or current region if the range is a single cell).

For help about using the Subtotal worksheet function in Visual Basic, see "Using Worksheet Functions in Visual Basic" in online Help.

Syntax

object.Subtotal(groupBy, function, totalList, replace, pageBreaks, summaryBelowData)

object

Required. The Range object.

groupBy

Required. The field to group by, as a one-based integer offset. For more information, see the example.

function

Required. The subtotal function. Can be one of xlAverage, xlCount, xlCountNums, xlMax, xlMin, xlProduct, xlStDev, xlStDevP, xlSum, xlVar, or xlVarP.

totalList

Required. An array of one-based field offsets, indicating the fields to which the subtotals are added. For more information, see the example.

replace

Optional. If True, existing subtotals are replaced. If False or omitted, existing subtotals are not replaced.

pageBreaks

Optional. True to create page breaks after each group, False or omitted if no page breaks are created.

summaryBelowData

Optional. If xlBelow or omitted, the summary goes below detail. If xlAbove, the summary goes above detail.

See Also

RemoveSubtotal Method.

Example

This example creates subtotals for the selection on Sheet1. The subtotals are sums grouped by each change in field one, with the subtotals added to fields two and three.

Worksheets("Sheet1").Activate
Selection.Subtotal groupBy:=1, function:=xlSum, _
    totalList:=Array(2, 3)