How to Use Subtotal Function in VBA (With Examples)

You can use the SUBTOTAL function in Excel to calculate aggregate statistics for only the visible cells in a sheet.

You can use the following basic syntax in VBA to use the SUBTOTAL function:

Sub FindSubtotal()
Range("A16") = WorksheetFunction.Subtotal(9, Range("B2:B11"))
End Sub

This particular example calculates the sum of the values in the cells visible in the range B2:B11 and outputs the result in cell A16.

Note that the first argument in the Subtotal method specifies which aggregation method to use where:

The following example shows how to use the Subtotal method in VBA in practice.

Example: How to Use Subtotal in VBA

Suppose we have the following dataset in Excel that contains information about various basketball players:

Now suppose we apply a filter to only show the rows where the team is equal to A or C:

We can create the following macro to calculate the sum of the visible cells in the points column and display the results in cell A16:

Sub FindSubtotal()
Range("A16") = WorksheetFunction.Subtotal(9, Range("B2:B11"))
End Sub

When we run this macro, we receive the following output:

Notice that cell A16 contains a value of 168.

This tells us that the sum of the visible cells in the points column is 168.

We can also change the value of the first argument in the Subtotal method to calculate a different metric.

For example, we can use a value of 1 to instead calculate the average of the visible cells in the points column:

Sub FindSubtotal()
Range("A16") = WorksheetFunction.Subtotal(1, Range("B2:B11"))
End Sub

When we run this macro, we receive the following output:

Notice that cell A16 contains a value of 24.

This tells us that the average of the visible cells in the points column is 24.

Note: You can find the complete documentation for the VBA Subtotal method here.

The following tutorials explain how to perform other common tasks in VBA: