totaling difficulties
tried posting a similar question on the newuser forum but since more people seem to view here thought it'd be worth trying to post here. i have a large worksheet with hundreds of names and then months of the year. there are no blank lines. what i want to do is total the figures (some cells are blank) under each month after each name. a name can appear once or many times. ideally what i want to do is at the change of a name insert 2 blank lines and use one line to total the figures for that name then have one as a separator. it was suggested i use subtotal function but i can't see how i can use it to add in blank lines, nor get it to calculate for each month for each name without manually typing in (or copying) a formula in thousands of cells. i was hoping there was a way to say on change of name calculate totals using numbers between last blank line and now. maybe an example would be more obvious: worksheet currently: name m1 m2 m3 aaaa 03 01 bbbb 01 01 02 bbbb 01 02 01 desired result: name m1 m2 m3 aaaa 03 01 total 03 01 bbbb 01 01 02 bbbb 01 02 01 total 02 03 03 suggestions appreciated, alison. -- alisonmacd ------------------------------------------------------------------------ alisonmacd's Profile: http://www.excelforum.com/member.php...o&userid=24323 View this thread: http://www.excelforum.com/showthread...hreadid=381133 |
Dim iLastRow As Long
Dim i As Long Columns("A:D").Subtotal GroupBy:=1, _ Function:=xlSum, _ TotalList:=Array(2, 3, 4), _ Replace:=True, _ PageBreaks:=False, _ SummaryBelowData:=True iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = iLastRow To 2 Step -1 If Right(Cells(i, "A").Value, 5) = "Total" Then Cells(i + 1, "A").EntireRow.Insert End If Next i -- HTH Bob Phillips "alisonmacd" wrote in message ... tried posting a similar question on the newuser forum but since more people seem to view here thought it'd be worth trying to post here. i have a large worksheet with hundreds of names and then months of the year. there are no blank lines. what i want to do is total the figures (some cells are blank) under each month after each name. a name can appear once or many times. ideally what i want to do is at the change of a name insert 2 blank lines and use one line to total the figures for that name then have one as a separator. it was suggested i use subtotal function but i can't see how i can use it to add in blank lines, nor get it to calculate for each month for each name without manually typing in (or copying) a formula in thousands of cells. i was hoping there was a way to say on change of name calculate totals using numbers between last blank line and now. maybe an example would be more obvious: worksheet currently: name m1 m2 m3 aaaa 03 01 bbbb 01 01 02 bbbb 01 02 01 desired result: name m1 m2 m3 aaaa 03 01 total 03 01 bbbb 01 01 02 bbbb 01 02 01 total 02 03 03 suggestions appreciated, alison. -- alisonmacd ------------------------------------------------------------------------ alisonmacd's Profile: http://www.excelforum.com/member.php...o&userid=24323 View this thread: http://www.excelforum.com/showthread...hreadid=381133 |
All times are GMT +1. The time now is 01:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com