How to calculate the Subtotals for a Range a data in Excel using VBA

There is an in-built feature in Excel called the Subtotal (under the Data tab) which when used can automatically insert subtotals and grand total for a selected range of data. However, if you are a VBA programmer, you can further automate this process using a simple, one line macro.

The image below explains it.

Calculate subtotals for a range of data in Excel using VBA

The Macro

Let us assume, I have a range of data with few columns (see the above image again). I want to show the Subtotal of Price for each product and finally at the last row show the grand total, with the click of a button.

Note: The result will be the same like the Subtotal feature that Excel provides. We are doing this using a Macro.

Option Explicit

Private Sub CommandButton1_Click()
    showSubTotal
End Sub

Sub showSubTotal()
    Worksheets("sheet1").Activate
    
    Range("A1:E" & Cells(Rows.Count, "E").End(xlUp).Row).Subtotal _
            GroupBy:=1, Function:=xlSum, TotalList:=Array(5, 4), _
                Replace:=True, PageBreaks:=False

End Sub

In the above macro, I am using the Range() method where I have defined a range from column A to E. This is fixed, kind of hardcoded. Therefore, every time I click the button, it will show the subtotal and grand total of that particular range, A to E.

Now here’s another scenario. I don’t want to show the subtotal of all the products, only a few. For example, I want to insert subtotal to selected products only, like Pens, Fax and Safety pins.

To do this I can use the same method (that I used in the above example) using the Selection property.

Option Explicit

Private Sub CommandButton1_Click()
    showSubTotal
End Sub

Sub showSubTotal()
    Worksheets("sheet1").Activate
    
    Selection.Subtotal _
        GroupBy:=1, Function:=xlSum, TotalList:=Array(5, 4), _
            Replace:=True, PageBreaks:=False
End Sub

Calculate subtotals for selected range in Excel using VBA

Remember, if you are using the Selection property, you have to first select a range and then click the button (or whatever method you are using). Or else it will throw an error.

← PreviousNext →