# Excel 2003 VBA Specify multiple column subtotals

I have a worksheet containing 4 columns.  I want to use the subtotal method of the range object.  The third and fourth columns contain numeric data.  I want the sum of each group for the 3rd column data and the max of each group for the 4th column data.  Does anyone have have a VBA code snippet to accomplish this?

A     B     C     D
x      x     1     4
x      x     2     3
x      x     3     2
x      x     4     1
------------------
Subtotal: 10   4
###### Who is Participating?

Commented:
Provided your data is as your example.  (number of rows not a problem)

then:
``````Sub TotCols()

Range("A1").Offset(Range("A1").CurrentRegion.Rows.Count, 2).FormulaR1C1 = "=subtotal(9,R[-" & Range("A1").CurrentRegion.Rows.Count & "]C:R[-1]C)"

Range("A1").Offset(Range("A1").CurrentRegion.Rows.Count - 1, 3).FormulaR1C1 = "=max(R[-" & Range("A1").CurrentRegion.Rows.Count - 1 & "]C:R[-1]C)"

End Sub
``````
0

Author Commented:
Thanks TinTombStone.  Unfortunately my data is a little more complicated than the example. There are multiple groups.  Here is an updated example:  Sorry that I did not give a proper example.

A     B        C     D
z      x        1     4
z      x        2     3
z      x        3     2
z      x        4     1
---------------------
Subtotal x: 10   4
z      y        6     9
z      y        7     8
z      y        8     6
z      y        9     7
---------------------
Subtotal y:  30   9

GrandTotal Z: 40  9
0

Commented:
Hoping someone can pick this up from here

I will be away till Monday
0

Commented:
Assume the sheet as below and try the code:
A     B        C     D
z      x        1     4
z      x        2     3
z      x        3     2
z      x        4     1
z      y        6     9
z      y        7     8
z      y        8     6
z      y        9     7

``````Sub SubTot()
Dim x As Long
'Set Rng = Range("A1:A" & UsedRange.Rows.Count)
a = 2
b = 2
For i = 2 To ActiveSheet.Rows.Count
If Range("A" & i).Value = "" Then Exit For
If Range("A" & i).Value <> Range("A" & i + 1).Value Or _
Range("B" & i).Value <> Range("B" & i + 1).Value Then
Rows(i + 1 & ":" & i + 3).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A" & i + 1).Value = "----------"
Range("A" & i + 2).Value = "Subtotal " & Range("B" & i).Value
Range("C" & i + 2).Value = "=SUM(C" & b & ":C" & i & ")"
Range("D" & i + 2).Value = "=MAX(D" & b & ":D" & i & ")"

If Range("A" & i).Value <> Range("A" & i + 4).Value Then
Rows(i + 4 & ":" & i + 6).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A" & i + 4).Value = "----------"
Range("A" & i + 5).Value = "total " & Range("A" & i).Value
Range("C" & i + 5).Value = "=SUM(C" & a & ":C" & i + 2 & ")/2"
Range("D" & i + 5).Value = "=MAX(D" & a & ":D" & i & ")"
Range("A" & i + 6).Value = "----------"
i = i + 6
a = i + 1
b = i + 1
Else
i = i + 3
b = i + 1
End If
End If
Next i
``````
0

Author Commented:
Thanks for your help chwong67.   Your solution worked for the example.  But I realized that the data was not always consistent in the number of rows for each subtotal group.

In the meantime I came up with a more generalized solution.  I used the subtotal method of the range object for column C.  For Column D I copied the subtotal formula from column C and changed the first argument from 9 to 4.
e.g. Cell C5 formula:  =Subtotal(9,C1:C4)
Cell D5 formula:  =Subtotal(4,D1:D4)
0

Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for irc200's comment http:/Q_27297615.html#36510437

for the following reason:

My solution solved my specific instance of the problem
0

Commented:
irc200's  question ended with  "Does anyone have have a VBA code snippet to accomplish this"

As irc200's solution does not seem to be a VBA solution, it can not answer the question
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.