How to Use the Subtotal Function in Excel and VBA

Defining a Range

In Microsoft Excel, a “range” of cells is a continuous group of cells across rows and columns. Here’s some valid examples of ranges with references to the cells:

Any single cell like H54, C3.

A part of any row Eg: D4:G4, H10:N10.

A part of any column Eg: D4:D9, H10:H20.

A combination of rows and columns/a table of any size Eg: A2:D20, G5:J15.

The Range Function 

In VBA we can refer to a range using the range function.

Syntax

Range(“<expression>”)

Where <expression>  is the columns and rows/cells that should be part of a range.

For Example: 

Range(“D4:G4”) – marked in yellow in the image below.

Range(“A6:D20”) – marked in green in the image below.

Range(“G5:K19”) – marked in blue in the image below.

Range ("<expression>") formula

Actions on Range

The following are some actions that can be performed on a range:

  1. Select a range
  2. Delete a range
  3. Clear contents of a range
  4. Apply some formula on a range of numeric values

Subtotal Formula

You might have worked with mathematical formulas in cells like sum, average, count, standard deviation, etc…  These are straightforward functions that do a calculation on the specified range of cells depending on whether the cells are visible/hidden (that is, if filters are applied).

Syntax:

Subtotal(<function number> , <range ref>)

Where function number indicates a corresponding calculation function to be performed (see the table below) and <range ref> refers to the range of cells to be considered for the calculation.

List of function numbers:

Function numFunction numFunction
All hidden values are includedAll hidden values are ignored
1101AVERAGE
2102COUNT
3103COUNTA
4104MAX
5105MIN
6106PRODUCT
7107STDEV
8108STDEVP
9109SUM
10110VAR
11111VARP
The range of cells to be considered for the calculation

Check out the difference between sum formula and subtotal formula:

Difference between a sum formula and a subtotal fomula
Difference between a sum formula and a subtotal fomula

Cell B12 uses a sum formula:

Cell B12 sum formula

Now both display the total of cells (B2 to B10). 162 is the value displayed in both the cells.

When a filter is applied on the “Price” column, we can see the subtotal formula considers only the filtered cells for calculation.

162 is the value displayed in both the cells.
Value of first parameter changed
To use this feature for average, count, standard deviation, etc., we just need to change the value of the first parameter as shown in the first image of this example.

Range.Subtotal Function Using VBA

The subtotal formula used in cells can be used in VBA code too, using two methods. 

Syntax 1:

Range(<expression>)=Subtotal (GroupByFunctionTotalList, [Replace], [PageBreaks], [SummaryBelowData])

Each of the parameters is explained in the table below:

NameParameter is Required / OptionalData type of ParameterDescription
GroupByRequiredLongThis is the field to group by, as a one-based integer offset.
FunctionRequiredXlConsolidationFunctionThis is the subtotal function.
TotalListRequiredVariantThis is an array of one-based field offsets, indicating the fields to which the subtotals are added.
ReplaceOptionalVariantTrue value is used to replace existing subtotals. It is the default value.
PageBreaksOptionalVariantTrue to add page breaks after each group. The default value is False.
SummaryBelowDataOptionalXlSummaryRowPlaces the summary data relative to the subtotal.
Worksheets("Mydemo").Activate 
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2, 3)

The program activates the specified sheet and calculates the sum of the selected cells or range that are filtered and visible.

Don’t worry if this Range.Subtotal method is confusing or not clear. We have a better method to use the same Subtotal function in VBA.

Syntax 2

Range(<cell reference>) = Application.WorksheetFunction.Subtotal(<function number>, Range(<range reference>))

Where: 

  1. <cell reference> is the cell number in which the return value of this function is to be displayed.
  2. <function number> is the number that decides what calculation needs to be done. The available values are explained in the “List of function numbers” table above.
  3. <range reference> is the range of cells that have to be considered for the calculation.

Examples

Display the Sum as a Subtotal in Another Cell

Let us do the same calculation which we did in the previous example.

Sub st_demo()
    ' declare necessary variable
    Dim subt
    
    ' calculate and store the subtotal value in a variable
    subt = Application.WorksheetFunction.Subtotal(9, Range("B2:B10"))
    
    'display the variable value in the cell
    Range("B13") = subt
    
End Sub

The output is calculated and displayed in the cell B13. It can also be displayed in a message box if required.

Formula is not visible in the formula bar.

Since this is achieved through VBA code, in this image above, the formula is not visible in the ’formula bar’ as in the previous examples.

Program to Display the Cheapest Food Item on a Menu

Sub st_demo()
    ' declare necessary variable
    Dim subt
    
    ' find the cheapest of the displayed menu items.
    subt = Application.WorksheetFunction.Subtotal(5, Range("B2:B10"))
    
    'display the variable value in the cell
    MsgBox subt &amp;amp; " Rupees is the minimum about required to purchase some food item from this store. "
    
End Sub

Please note that the function number used in this program is “5” which stands for “Min” value.

Displaying the minimum value

Now, I’ll run the same program after removing the single-digit values using a filter:

Running the program after removing single-digit values after using a filter
Minimum value displayed

Conclusion

The subtotal formula/function can be useful especially when several rows/columns are hidden because of filters. 

Pros of this method:

  1. Using a subtotal function in VBA can be useful when the value keeps changing during the runtime of lengthy code and this value deviates the flow of the program.

Cons of this method:

  1. There is no “undo” option when you manipulate data with VBA code.
  2. The formula is not visible. Because of this, the user may not know what or why a particular value is displayed.
  3. As this is not achieved through an Excel formula that auto-calculates values, the value will remain the same in the cell until your code is rerun after every new filter option.

Leave a Reply

Your email address will not be published. Required fields are marked *