Solved

# Excel 2003 VBA Specify multiple column subtotals

Posted on 2011-09-08
1,089 Views
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
0
Question by:irc200
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 3

LVL 6

Accepted Solution

TinTombStone earned 250 total points
ID: 36503028
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 Comment

ID: 36503222
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

LVL 6

Expert Comment

ID: 36503779
Hoping someone can pick this up from here

I will be away till Monday
0

LVL 9

Assisted Solution

chwong67 earned 250 total points
ID: 36509024
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

Assisted Solution

irc200 earned 0 total points
ID: 36510437
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 Comment

ID: 36519798
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

LVL 6

Expert Comment

ID: 36519799
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
###### Suggested Courses
Course of the Month8 days, 9 hours left to enroll