VBA Code for Subtotal in Excel (3 Ways)

Consider the following dataset. We have 3 Products named Cricket Ball, Football, and Tennis Ball, and their Sales value across 3 months, January, February, and March. We will calculate the Subtotal for each product for each month.

Method 1 – Embed VBA Macro to Calculate Subtotals in Excel

Steps:

• Press Alt + F11 on your keyboard or go to the Developer tab and select Visual Basic to open the Visual Basic Editor.

• In the pop-up code window, click Insert and select Module.

• Copy the following code and paste it into the code window.

Sub GenerateSubtotal()
Dim iColumn As Integer
Dim iValue As Integer
Dim xValue As Integer
Application.ScreenUpdating = False
iValue = 5
xValue = iValue
Range("C5").CurrentRegion.Offset(1).Sort Range("C6"), 1
Do While Range("C" & iValue) <> ""
    If Range("C" & iValue) <> Range("C" & (iValue + 1)) Then
    Rows(iValue + 1).Insert
    Range("C" & (iValue + 1)) = "Subtotal " & Range("C" & iValue).Value
    For iColumn = 6 To 8 'Columns to Calculate Sum
    Cells(iValue + 1, iColumn).Formula = "=SUM(R" & xValue & "C:R" & iValue & "C)"
    Next iColumn
    Range(Cells(iValue + 1, 1), Cells(iValue + 1, 8)).Font.Bold = True
    iValue = iValue + 2
    xValue = iValue
    Else
    iValue = iValue + 1
    End If
    Loop
Application.ScreenUpdating = True
End Sub

• Press F5 on your keyboard or select Run then Run Sub/UserForm. You can also just click on the small Run icon in the sub-menu bar to run the macro.

• Here’s the result.

Sub GenerateSubtotal()

Dim iColumn As Integer
Dim iValue As Integer
Dim xValue As Integer

Application.ScreenUpdating = False

iValue = 5
xValue = iValue

Range("C5").CurrentRegion.Offset(1).Sort Range("C6"), 1

Do While Range("C" & iValue) <> ""
    If Range("C" & iValue) <> Range("C" & (iValue + 1)) Then
    Rows(iValue + 1).Insert
    Range("C" & (iValue + 1)) = "Subtotal " & Range("C" & iValue).Value
    For iColumn = 6 To 8 'Columns to Calculate Sum
    Cells(iValue + 1, iColumn).Formula = "=SUM(R" & xValue & "C:R" & iValue & "C)"
    Next iColumn
    Range(Cells(iValue + 1, 1), Cells(iValue + 1, 8)).Font.Bold = True
    iValue = iValue + 2
    xValue = iValue
    Else
    iValue = iValue + 1
    End If
    Loop

Application.ScreenUpdating = True

End Sub

Method 2 – Insert VBA Code to Generate a Subtotal with a Button

Create a Button to Calculate Subtotal:

• Go to the Developer tab.
• Click Insert and select Command Button under the ActiveX Controls group.

• The cursor changes to a plus symbol (+). Drag and release the symbol to create a button of any size anywhere in your spreadsheet.

• Here’s what our button looks like.

• To modify the caption of the button, right-click on the button. A list of options will appear. Select Properties.
  Make sure you keep the Design Mode enabled, or you won’t be able to change the button’s appearance.

• A Properties window will come up. Write any name that you want to give your button in the Caption In our case, we named our button Click to Subtotal.

Execute VBA Code with the Button to Generate Subtotals:

• Right-click on the button (and make sure the Design Mode is turned on) and select View Code from the option list. You can also double-click the button to go to the code window.

• You will be redirected to the code window. Copy the following code and paste it there.

Option Explicit
Private Sub CommandButton1_Click()
    CalculateSubtotal
End Sub
Sub CalculateSubtotal()
    Worksheets("Subtotal Button").Activate
    Range("C4:H" & Cells(Rows.Count, "H").End(xlUp).Row).Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(4, 5, 6), Replace:=True, PageBreaks:=False
End Sub

• Save this macro code.
• Go to the worksheet.
• Click the button. Don’t forget to disable the Design Mode this time.

• After you click the button, you will see that the dataset now consists of the Subtotal sales value of each set of products.

Option Explicit

Private Sub CommandButton1_Click()
    CalculateSubtotal
End Sub

Sub CalculateSubtotal()

Worksheets("Subtotal Button").Activate
Range("C4:H" & Cells(Rows.Count, "H").End(xlUp).Row).Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(4, 5, 6), Replace:=True, PageBreaks:=False

End Sub

Read More: How to Insert Subtotals in Excel

Method 3 – VBA to Populate the Subtotal for a Specific Range of Data with a Button

Steps:

• Create a button, modify it, and assign a macro to it as in the previous section.
• Copy the following code and paste it into the code window of the button.

Option Explicit
Private Sub CommandButton1_Click()
    GenerateSubtotal
End Sub
Sub GenerateSubtotal()
    Worksheets("Subtotal Selected").Activate
    Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(4, 5, 6), Replace:=True, PageBreaks:=False
End Sub

• Save this macro code and go to the worksheet of interest.
• Select the range you want to subtotal.
• Click on the button.

• We will generate the Subtotal of the sales value only for Cricket Ball and Football, so we dragged through those products for the selection and clicked the button.

Option Explicit

Private Sub CommandButton1_Click()
    GenerateSubtotal
End Sub

Sub GenerateSubtotal()

Worksheets("Subtotal Selected").Activate
    Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(4, 5, 6), Replace:=True, PageBreaks:=False

End Sub

Download the Workbook

Related Articles

• How to Use SUBTOTAL in Excel with Filters
• How to Make Subtotal and Grand Total in Excel
• How to Sort Subtotals in Excel
• How to Remove Subtotals in Excel

<< Go Back To Subtotal in Excel | Learn Excel