Using SUBTOTAL Function in Excel VBA for Aggregation

2023年6月25日

The SUBTOTAL function allows you to perform various aggregations by specifying aggregation conditions. By choosing the aggregation method, you can calculate not only the total value but also other metrics such as average, maximum, minimum, and standard deviation.
Moreover, the flexibility of SUBTOTAL function enables you to include or exclude hidden cells during aggregation, providing versatile options.

Explanation of SUBTOTAL Function

Method of SortFields Collection
　WorksheetFunction.Subtotal(aggregation_method, aggregation_range1[, aggregation_range2, …])
The SUBTOTAL function offers 11 types of aggregation methods, which can be specified by the number corresponding to the first or second column in the table below.

Including hidden cells	Excluding hidden cells	Aggregation method	Corresponding function
1	101	Average	AVERAGE
2	102	Number of cells with values	COUNT
3	103	Number of cells excluding blanks	COUNTA
4	104	Maximum	MAX
5	105	Minimum	MIN
6	106	Product	PRODUCT
7	107	Standard deviation of a sample	STDEV
8	108	Standard deviation of the entire population	STDEVP
9	109	Sum	SUM
10	110	Predicted variance of a sample	VAR
11	111	Variance of the entire population	VARP

Explanation of Sample Application Using SUBTOTAL Function

When you click the “The number of people working in New York" button, it filters the employees located in New York using AutoFilter and displays the count of employees in a dialog box.

When you click the “The average commuting allowance for people working in New York" button, it filters the employees located in New York using AutoFilter and displays the average commute allowance of the filtered employees in a dialog box.

Source Code of the Sample Application
　　　　　　　　　Here are the steps to create the app

Source code for the “Number of Employees in Tokyo" button

Option Explicit
  
Sub Button1_Click()

   'Specify the worksheet
    Dim Sh1 As Worksheet
    Set Sh1 = ThisWorkbook.Worksheets("Sheet1")
    
   'Define variables
    Dim workEndR1, Ninzu As Long
    
   'Get the last row of data
    workEndR1 = Sh1.Cells(Rows.Count, 2).End(xlUp).Row
    
   'Specify the data range for autofilter
    Dim Rng1 As Range
    Set Rng1 = Range(Sh1.Cells(5, 2), Sh1.Cells(workEndR1, 7))
    
   'Extract employees working in New York City using autofilter.
    Rng1.AutoFilter Field:=5, Criteria1:="New York City, New York"
    
   'Specify the data range for aggregation
    Dim Rng2 As Range
    Set Rng2 = Range(Sh1.Cells(6, 6), Sh1.Cells(workEndR1, 6))
   
   'Specify the aggregation method as "3" (count) and aggregate the number of relevant people.
    Ninzu = WorksheetFunction.Subtotal(3, Rng2)
    
   'Display a dialog box showing the number of people
    MsgBox "The number of people working in New York: " & Ninzu & " people"

   'Process branching based on the filter status
    If ActiveSheet.AutoFilterMode = True Then
        'Remove autofilter
        Rng1.AutoFilter

    ElseIf ActiveSheet.AutoFilterMode = False Then
        'Do nothing if autofilter is already disabled
  
    End If

End Sub

Source code for the “Average Commute Allowance of Employees in Tokyo" button

Sub Button2_Click()

   'Specify the worksheet
    Dim Sh1 As Worksheet
    Set Sh1 = ThisWorkbook.Worksheets("Sheet1")
    
   'Define variables
    Dim workEndR1, Heikin As Long
    
   'Get the last row of data
    workEndR1 = Sh1.Cells(Rows.Count, 2).End(xlUp).Row
    
   'Specify the data range for autofilter
    Dim Rng1 As Range
    Set Rng1 = Range(Sh1.Cells(5, 2), Sh1.Cells(workEndR1, 7))
    
   'Extract employees working in New York City using autofilter.
    Rng1.AutoFilter Field:=5, Criteria1:="New York City, New York"
    
   'Specify the data range for aggregation
    Dim Rng2 As Range
    Set Rng2 = Range(Sh1.Cells(6, 7), Sh1.Cells(workEndR1, 7))

   'Specify the aggregation method as "1" (average) and aggregate the average commuting allowance of the relevant individuals.
    Heikin = WorksheetFunction.Subtotal(1, Rng2)
    
   'Display a dialog box showing the average amount
    MsgBox "The average commuting allowance for people working in New York: " & vbCrLf & "$ " & Format(Heikin, "#,###")
       
   'Process branching based on the filter status
    If ActiveSheet.AutoFilterMode = True Then
        'Remove autofilter
        Rng1.AutoFilter
        
    ElseIf ActiveSheet.AutoFilterMode = False Then
        'Do nothing if autofilter is already disabled
        
    End If

End Sub

VBA,Aggregation processingSubtotal,VBA,集計

Posted by CoderM