MrExcel Publishing
Your One Stop for Excel Tips & Solutions

VBA code to subtotal.

Posted by J on October 19, 2000 4:34 PM

Anybody know a VBA code that will use subtotal to add up the total line items a particular person has. The totals can vary from week to week. After that it needs to sum up the total amount of comissions for all line items. I have Buyer, line items, and comissions in three different columns. Can anybody help???

Posted by Celia on October 19, 2000 5:19 PM

Why not just use Excel's Sub-Total tool?
If you want a macro, you could record your actions.
The following macro was recorded with the macro recorder and then slightly amended. I have assumed you need a count of each buyer's line items and a sum of the commissions.

Sub Sub_Tot()
Intersect(ActiveSheet.UsedRange, Range("A:B")).Select
Selection.Subtotal GroupBy:=1, Function:=xlCount, TotalList:=Array(2), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Intersect(ActiveSheet.UsedRange, Range("A:C")).Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
End Sub