PDA

View Full Version : Sleeper: Subtotal



Cass
09-25-2005, 09:23 AM
how to use subtotal in VB? http://vbaexpress.com/forum/images/smilies/102.gif

.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2, 3)

what mean every part in this method?!
i understand only the function: part http://vbaexpress.com/forum/images/smilies/doh.gif

If i have autofiltered data range("K2:K355") how i use subtotal?

Jacob Hilderbrand
09-25-2005, 10:41 AM
GroupBy Required Long. The field to group by, as a one-based integer offset. For more information, see the example.

TotalList Required Variant. An array of 1-based field offsets, indicating the fields to which the subtotals are added. For more information, see the example.

You need to tell Excel what column to group by, and what column to add up for each group. So what column has your categories that you want to group, and is K your values?

Maybe post your workbook and we can get the setting correct for you.

Cass
09-25-2005, 12:46 PM
I have look this excel subtotal help but maiby is my english so bad and i dont understand this example there.
Ok i want use subtotal one part of this tread explained probelm
http://www.vbaexpress.com/forum/showthread.php?t=5353

Jacob Hilderbrand
09-25-2005, 02:06 PM
Assuming you want to group by Col B and sum Col K then...

B=2 (2nd Column)
K=11 (11th Column)



Range("A1:N355").Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(11), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

Cass
09-26-2005, 01:46 AM
Assuming you want to group by Col B and sum Col K then...

B=2 (2nd Column)
K=11 (11th Column)



Range("A1:N355").Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(11), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True



Ok now i understand subtotal method :clap: but is possible without GroupBy and using the GrandTotal value in VB or sum only K column :think:

Another question how to remove subtotal after calculatins :doh:

Cass
09-27-2005, 05:41 AM
Any idea remove subtotal rows after calculations?

Jacob Hilderbrand
09-27-2005, 09:16 AM
To remove the subtotals...



Range("A:N").RemoveSubtotal