Bottom line: The SUBTOTAL Metrics Macro creates a summary table with SUBTOTAL formulas for each calculation type in the SUBTOTAL function.
Skill level: Intermediate
The SUBTOTAL Metrics Macro
During one of the VBA webinars I ran last week, Michelle had a question about creating SUBTOTAL formulas with a macro. This gave me an idea for writing a macro to create the SUBTOTAL formulas for each calculation type. The macro creates a summary table that includes each calculation type in the SUBTOTAL function for a specified range.
Checkout my post on an explanation of the SUBTOTAL function (including some quick tips for writing the formulas), if you are not familiar with it yet.
Here is a video clip from the live meeting where I share a macro that adds a SUBTOTAL formula to the bottom of a range, and I also show the SUBTOTAL Metrics Macro.
How the SUBTOTAL Metrics Macro Works
Here is a quick screencast of how the macro works:
The macro will create the SUBTOTAL formulas for each calculation type, starting in the Active Cell. Here's how it works.
- Select a cell where you want to place the summary table. The output range uses 2 columns and 12 rows.
- Run the macro from the Macros window (Developer/View > Macros), or create a ribbon button to run it.
- If any data exists in the output range, a message box will appear asking if you want to override the existing data.
- An input box appears and prompts you to select the ref range for the SUBTOTAL formulas. This is the range of cells (column) that you want to perform the calculation on.
- Press OK and the SUBTOTAL Metrics table will be created in the sheet. A SUBTOTAL formula will be created for each of the 11 calculation types.
With just a few clicks we can quickly create a summary report for a column of data. The formulas in the report will automatically update as we apply filters to the data range. Again, this is very handy for tying out numbers to other reports and pivot tables.
The VBA Code & Download File
You can download the workbook that contains the code below, and add it to your Personal Macro Workbook.
SUBTOTAL Metrics Macro.xlsm (20.6 KB)
Here is the VBA code for the SUBTOTAL Metrics Macro.
Sub Subtotal_Metrics() 'This macro creates formulas for all function types in 'the SUBTOTAL function. The output starts in the activecell. 'The user is prompted to select a range to include 'as the reference for the SUBTOTAL formulas. 'Author: Jon Acampora 'Source: https://www.excelcampus.com/vba/subtotal-metrics-macro/ Dim rRef As Range Dim sFuncName() As String Dim sFuncNum() As String Dim lRow As Long Dim lRowCnt Dim vbAnswer As VbMsgBoxResult Dim lFunc As Long Dim lFuncType As Long 'The following contstant determines the Func Number series 'This can be changed to True/False to set the func_num series 'True: func_num 1-11 to include hidden rows 'False: func_num 101-111 to ignore hidden rows Const bIncludeHidden As Boolean = False 'Set variables - The order of the arrays can be changed sFuncName = Split("Sum:,Average:,Count:,CountA:,Min:,Max:,Product:,STD.S:,STD.P:,Var.S:,Var.P:", ",") sFuncNum = Split("9,1,2,3,5,4,6,7,8,10,11", ",") 'The number of metrics listed in the table can be changed. 'Change to 5 to only include everything up to Max. lRowCnt = 10 'Check if any cells in the destination range are used and warn the user. If WorksheetFunction.CountA(ActiveCell.Offset(0, -1).Resize(11, 2)) > 0 Then vbAnswer = MsgBox("The output cells are not blank. " _ & "Do you want to continue and override the existing values in range: " _ & ActiveCell.Offset(0, -1).Resize(9, 2).Address & "?", _ vbYesNo, "Subtotal Metrics") End If If bIncludeHidden Then lFuncType = 0 Else lFuncType = 100 End If 'Continue running the macro if user says Yes or cells are blank. If vbAnswer = vbYes Or vbAnswer = 0 Then On Error Resume Next 'Display an input box for the user to select the reference range for the formula. Set rRef = Application.InputBox( _ Prompt:="Select the range for the SUBTOTAL formula", _ Title:="Subtotal Metrics", Type:=8) On Error GoTo 0 If Not rRef Is Nothing Then 'Loop through all metrics in the arrays For lRow = 0 To lRowCnt lFunc = CLng(sFuncNum(lRow)) + lFuncType ActiveCell.Offset(lRow + 1).Formula = _ "=SUBTOTAL(" & lFunc & ", " & rRef.AddressLocal(True, True, xlR1C1) & ")" ActiveCell.Offset(lRow + 1, -1).Value = sFuncName(lRow) Next lRow 'Add title to summary table On Error Resume Next With ActiveCell.Offset(0, -1) .Value = rRef.Offset(-1).Resize(1, 1).Value & " Metrics" .Font.Bold = True End With On Error GoTo 0 'Format numbers ActiveCell.Offset(1).Resize(lRowCnt + 1).NumberFormat = _ rRef(1).Resize(1, 1).NumberFormat ActiveCell(lRowCnt + 1).Select End If End If End Sub
Modifying the Macro
The macro can be modified to fit your needs. Here are a few examples.
Exclude Unused Metrics
By default, the macro will list all 11 SUBTOTAL calculation types. You can modify the code to exclude some of the metrics. For example, I rarely use the Product, standard deviation, and variance calculations.
There are two arrays in the macro that contain the list of calculation type names and function numbers. The strings that make up these arrays can be modified to include/exclude functions. You can also change the order of how the calculations are displayed.
Here is the code for the arrays that exclude Product, STDEV, and VAR.
sFuncName = Split("Sum:,Average:,Count:,CountA:,Min:,Max:", ",") sFuncNum = Split("9,1,2,3,5,4", ",")
Notice that I put Sum first in the list, even though it is calculation number 9. Sum is usually the most important calculation type, and I like to have it at the top. This is personal preference and you can modify the arrays to any order you like. Just make sure the order of the function names and numbers match in the strings that create each array.
Include/Ignore Hidden Rows
The function number can be changed to include/ignore hidden rows. The use of 1-11 or 101-111 is determined by a Constant named bIncludeHidden. You can change the value to True to include hidden rows (1-11) or ignore hidden rows (101-111).
'True: func_num 1-11 to include hidden rows 'False: func_num 101-111 to ignore hidden rows Const bIncludeHidden As Boolean = False
Number Formatting of the Output Range
The macro also changes the number formatting of the output range to match the number formatting of the reference range. This can be changed in the following line of code as well.
ActiveCell.Offset(1).Resize(lRowCnt + 1).NumberFormat = _ rRef(1).Resize(1, 1).NumberFormat
How to Use the Macro in Your Workbooks
This macro can be copied into your Personal Macro Workbook, and used on any Excel file you have open. We can also add a custom button to the ribbon to run the macro anytime.
Checkout my free video series on the Personal Macro Workbook for more details on how to implement this.
Please leave a comment below with any questions. Thank you! 🙂