MAKE SUBTOTAL APPLICATION ON AND OFF IN MACRO

[K]

Hi I want to get subtotals in the range (B1:K200) by every value
change in col B. Is there way that I can have some sort of macro on a
Toggle Button that when I press button then it should put subtotals in
the range but when I press it again then it should remove the
subtotals.

 

[Anant.Basant]

Hi I want to get subtotals in the range (B1:K200) by every value
change in col B.  Is there way that I can have some sort of macro on a
Toggle Button that when I press button then it should put subtotals in
the range but when I press it again then it should remove the
subtotals.

Hi K

You can use the following code:

Sub Add_SubTotals()
Dim r As Range

Set r = Range("$A$1:$K$200")

r.Sort Key1:=Range("B2"), Order1:=xlAscending, _
Header:=xlYes

r.Subtotal GroupBy:=2, Function:=xlSum, _
TotalList:=Array(7), Replace:=True, _
PageBreaks:=False, SummaryBelowData:=True


End Sub

Sub Remove_SubTotals()
Dim r As Range
Set r = Range("$A$1:$K$200")
r.RemoveSubtotal
End Sub

Please note that change this code to your environment like change the
Array(7) to whatever column number your total has to appear in. If
totals in multiple columns are required, you can write Array(7,9) etc.
Also please add a button to your worksheet by going to View/Toolbars
and opening the Forms toolbar, add a button and assign macros to those
buttons.

Hope that helps.

Thanks
Anant

 

[K]

Hi K

You can use the following code:

Sub Add_SubTotals()
    Dim r As Range

    Set r = Range("$A$1:$K$200")

    r.Sort Key1:=Range("B2"), Order1:=xlAscending, _
                 Header:=xlYes

    r.Subtotal GroupBy:=2, Function:=xlSum, _
               TotalList:=Array(7), Replace:=True, _
               PageBreaks:=False, SummaryBelowData:=True

End Sub

Sub Remove_SubTotals()
    Dim r As Range
    Set r = Range("$A$1:$K$200")
    r.RemoveSubtotal
End Sub

Please note that change this code to your environment like change the
Array(7) to whatever column number your total has to appear in. If
totals in multiple columns are required, you can write Array(7,9) etc.
Also please add a button to your worksheet by going to View/Toolbars
and opening the Forms toolbar, add a button and assign macros to those
buttons.

Hope that helps.

Thanks
Anant

Thanks Anant.