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

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

A B C D

x x 1 4

x x 2 3

x x 3 2

x x 4 1

------------------

Subtotal: 10 4

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

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
```

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)

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

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.

then:

Open in new window