Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Totaling Colored Cells | Excel Discussion (Misc queries) | |||
Totaling rows of text | Excel Worksheet Functions | |||
totaling times past 24 hours | Excel Worksheet Functions | |||
Totaling Cells in Worksheets | Excel Worksheet Functions | |||
in excel totaling weekly hours military time | Excel Worksheet Functions |