Need 2 Excel Subtotals Using VBA

[cluss27]

I am looking to subtotal multiple columns some with Sum, others with Average. I am using the following code, but, it is not working, it replaces the sums with the Averages, and I cannot get everything to display properly.

Sub Subtotal()
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18), Replace:=False, PageBreaks:=False, _
SummaryBelowData:=True
Selection.Subtotal GroupBy:=1, Function:=xlAverage, TotalList:=Array(19, 20), Replace:=False, PageBreaks:=False, _
SummaryBelowData:=True
End Sub

Can anyone assist me with this problem?

 

[judgeh59]

if you truly want a VBA Solution then there a VBA group just for that....
VBA Visual Basic for Applications (Microsoft)...
a repost into that group would be a good idea....

I'm also looking into a solution...

Ernest

Be Alert, America needs more lerts

 

[SkipVought]

hi,

The Subtotal feature can be used as the initital setup for ONE of your aggregations types.

Then comes VBA. Use VBA to COPY the SUBTOTAL function in each row to the column of interest for some other aggregation type, and then CHANGE the [function_number] accordingly. Turn on your macro recorder and then post in forum707 for help customizing if you need help. I'd use row 1 to contain the [function_number], and just reference the row 1 value in the SUBTOTAL function.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]