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