totaling difficulties

[alisonmacd]

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.

 

[Bob Phillips]

Dim iLastRow As Long
Dim i As Long

Columns("A").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