Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Subtotal
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 111
Default Subtotal
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Subtotal
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


"Helmut" wrote:

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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 111
Default Subtotal
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







"Joel" wrote:

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


"Helmut" wrote:

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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Subtotal
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

"Helmut" wrote:

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







"Joel" wrote:

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


"Helmut" wrote:

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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 111
Default Subtotal
Joel,
Not quite...may I send you and exel-sheet where I better explained?
where to:
thanks
Helmut

"Joel" wrote:

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

"Helmut" wrote:

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







"Joel" wrote:

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


"Helmut" wrote:

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
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Subtotal
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,
Not quite...may I send you and exel-sheet where I better explained?
where to:
thanks
Helmut

"Joel" wrote:

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

"Helmut" wrote:

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







"Joel" wrote:

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


"Helmut" wrote:

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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Subtotal To Include Item Description On Subtotal Line Tickfarmer Excel Discussion (Misc queries) 2 February 23rd 10 07:56 PM
pasting to subtotal lines without replacing hidden -non-subtotal l harleydiva67 Excel Discussion (Misc queries) 1 October 12th 06 06:02 PM
Bolding the subtotal lines automaticlly When using the Subtotal fu 06Speed6 New Users to Excel 2 October 5th 06 03:52 PM
Subtotal of Subtotal displays Grand Total in wrong row Thomas Born Excel Worksheet Functions 5 January 6th 05 01:46 PM
Sort, Subtotal, Label Subtotal, Insert row Teak Excel Programming 2 April 8th 04 04:14 PM


All times are GMT +1. The time now is 03:51 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement