Subtotal

  • Thread starter Thread starter Helmut
  • Start date Start date
H

Helmut

I have: A B C
go no 5
go wa 7
go wa 3
go ni 9
an op 8
an yu 4
an yu 8
an ss 9

I need total by A and total by B within A

total go no 5
total go wa 10
total go ni 9
Is this possible within a macro?

thanks
 
Sub combinerows()

LastRow = Range("A" & Rows.Count).End(xlUp).Row
Set sortRange = Rows("1:" & LastRow)
sortRange.Sort _
key1:=Range("A1"), _
order1:=xlAscending, _
key2:=Range("B1"), _
order2:=xlAscending, _
Header:=xlNo

RowCount = 1
Do While Range("A" & RowCount) <> ""
If Range("A" & RowCount) = Range("A" & (RowCount + 1)) And _
Range("B" & RowCount) = Range("B" & (RowCount + 1)) Then

Range("C" & RowCount) = Range("C" & RowCount) + _
Range("C" & (RowCount + 1))
Rows(RowCount + 1).Delete
Else
RowCount = RowCount + 1
End If
Loop

End Sub
 
Joel, sorry I was not quite explicit enough:

total go no 5
total go wa 10
total go ni 9
gtotal go 24 <---- I missed this part

thanks
 
Sub combinerows()

LastRow = Range("A" & Rows.Count).End(xlUp).Row
Set sortRange = Rows("1:" & LastRow)
sortRange.Sort _
key1:=Range("A1"), _
order1:=xlAscending, _
key2:=Range("B1"), _
order2:=xlAscending, _
Header:=xlNo

RowCount = 1
Do While Range("A" & RowCount) <> ""
If Range("A" & RowCount) = Range("A" & (RowCount + 1)) And _
Range("B" & RowCount) = Range("B" & (RowCount + 1)) Then

Range("C" & RowCount) = Range("C" & RowCount) + _
Range("C" & (RowCount + 1))
Rows(RowCount + 1).Delete
Else
RowCount = RowCount + 1
End If
Loop

End Sub

Columns("A").Insert

RowCount = 1
StartRow = RowCount
Do While Range("B" & RowCount) <> ""
If Range("B" & RowCount) <> Range("B" & (RowCount + 1)) Then
Rows(RowCount + 1).Insert
Range("A" & (RowCount + 1)) = Range("B" & RowCount) & _
" Total"
Range("C" & (RowCount + 1)).Formula = _
"=Sum(C" & StartRow & ":C" & RowCount & ")"
RowCount = RowCount + 2
StartRow = RowCount
Else
If Range("B" & (RowCount + 1)) <> "" Then
Range("A" & RowCount) = "Total"
End If
RowCount = RowCount + 1
End If
Loop

End Sub
 
Joel,
Not quite...may I send you and exel-sheet where I better explained?
where to:
thanks
Helmut
 
I found two problems that I fixed.


Sub combinerows()

LastRow = Range("A" & Rows.Count).End(xlUp).Row
Set sortRange = Rows("1:" & LastRow)
sortRange.Sort _
key1:=Range("A1"), _
order1:=xlAscending, _
key2:=Range("B1"), _
order2:=xlAscending, _
Header:=xlNo

RowCount = 1
Do While Range("A" & RowCount) <> ""
If Range("A" & RowCount) = Range("A" & (RowCount + 1)) And _
Range("B" & RowCount) = Range("B" & (RowCount + 1)) Then

Range("C" & RowCount) = Range("C" & RowCount) + _
Range("C" & (RowCount + 1))
Rows(RowCount + 1).Delete
Else
RowCount = RowCount + 1
End If
Loop

Columns("A").Insert

RowCount = 1
StartRow = RowCount
Do While Range("B" & RowCount) <> ""
If Range("B" & RowCount) <> Range("B" & (RowCount + 1)) Then
Range("A" & RowCount) = "Total"
Rows(RowCount + 1).Insert
Range("A" & (RowCount + 1)) = Range("B" & RowCount) & _
" Total"
Range("D" & (RowCount + 1)).Formula = _
"=Sum(D" & StartRow & ":D" & RowCount & ")"
RowCount = RowCount + 2
StartRow = RowCount
Else
If Range("B" & (RowCount + 1)) <> "" Then
Range("A" & RowCount) = "Total"
End If
RowCount = RowCount + 1
End If
Loop

End Sub
 
Joel,
Thanks, I fixed that too and it does work and give me TOTALS, BUT it does
not maintain the details like when you use the "subtotal" routine in Excel.
what I really need here is a Subtotal within a subtotal with the details
maintained that make up the total.
I laid it all out nicely to follow on a worksheet. If possible, could you
give me a place where to send this sheet to, for you to take a look at it?
I surely would appreciate it very much.
Helmut
 
joel dot warburg at itt dot com

Helmut said:
Joel,
Thanks, I fixed that too and it does work and give me TOTALS, BUT it does
not maintain the details like when you use the "subtotal" routine in Excel.
what I really need here is a Subtotal within a subtotal with the details
maintained that make up the total.
I laid it all out nicely to follow on a worksheet. If possible, could you
give me a place where to send this sheet to, for you to take a look at it?
I surely would appreciate it very much.
Helmut
Click to expand...
 
Why VBA?
Why not just use Data -> Subtotal At each change in A Subtotal C.
Then Data Subtotal At each change in B Subtotal C and uncheck Remove
Existing Subtotals.
 
Back
Top