Table of Contents
The Subtotal function in Visual Basic for Applications (VBA) is a useful tool that can be used to quickly calculate values from a range of cells. It can be used to calculate sums, counts, averages, and more. It can also be used to ignore hidden rows in a range. To use it, one must first define a range of cells, then call the Subtotal function with the appropriate parameters. Examples of how to do this have been provided in this article.
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:
- 1: AVERAGE
- 2: COUNT
- 3: COUNTA
- 4: MAX
- 5: MIN
- 6: PRODUCT
- 7: STDEV
- 8: STDEVP
- 9: SUM
- 10: VAR
- 11: VARP
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.
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 .