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**.

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.

**Additional Resources**

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

VBA: How to Sum Values in Range

VBA: How to Calculate Average Value of Range

VBA: How to Count Number of Rows in Range