MAKE SUBTOTAL APPLICATION ON AND OFF IN MACRO
 

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.

MAKE SUBTOTAL APPLICATION ON AND OFF IN MACRO
 

On Apr 19, 1:38*am, K wrote:
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

MAKE SUBTOTAL APPLICATION ON AND OFF IN MACRO
 

On 19 Apr, 02:10, wrote:
On Apr 19, 1:38*am, K wrote:

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

Thanks Anant.