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
irc200Asked:
Who is Participating?
 
TinTombStoneCommented:
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

Open in new window

0
 
irc200Author 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
 
TinTombStoneCommented:
Hoping someone can pick this up from here

I will be away till Monday
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
chwong67Commented:
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

Open in new window

0
 
irc200Author 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
 
irc200Author 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
 
TinTombStoneCommented:
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.

All Courses

From novice to tech pro — start learning today.