Totaling manual subtotals

[fosterp]

Hi
I have a spreadhseet with lots of manual subtotals, probably about 30.
I've tried to figure out a macro using a loop to add all subtotals to rpoduce a total.
e.g. say subtotals are in a5, a8, a12 etc.
I could manually find all subtotals and do a massive =sum(a5,a8 etc. )
However, please could someone show me how to write a amcro to do this.
I have some code below. I just need someone to help me fill in the gaps.
Code

Sub suBtot()
Dim cell As Range
Dim r As Range
Dim s As Range
For Each cell In Range([a1], [a444])
If IsEmpty(cell) = False Then
Set r = cell.Offset(0, 4).End(xlDown) - where subtotals are.
'Summing cells ( subtotals )
cells(1,400).value = ' formula that would add up all subtotals.) i.e.
end if
next

[Oorang]

Are the sub totals all in their own column?

[Bob Phillips]

Surely, adding subtotals also adds a grand total?

[fosterp]

The subtotals were not calculated using

Data, Subtotal

otherwise, it would have been easy. I inheriuted the spreadsheet from another user and the subtotals were calculated in a previous program and then exported to Excel, the subtotals are thwerefore values and not formulae.

Subtotals, I created using a macro.

Now I just want excel to loop thru each subtotal and add them in a min total box. e.g. a550.

No, each subtotal is duireclty beneath the figures that make it up.

eg. a20 subtotal = sum(a10:19)

a40 subtotal = sum(a30:a33)

There is no observable pattern to how many numbers make up a subtotal. i hope this all makes sense.

[Bob Phillips]

Do a Find/Replace on column A and replace SUM( with SUBTOTAL(9, and then add another =SUBTOTAL(9,A1:A40) at the end.

[rory]

If you only have one level of subtotals, you could just use a sum formula and divide by 2?

[Bob Phillips]

And I thought my solution was simplistic :rofl:

[rory]

:) I figured it might be that easy...

[fosterp]

xld

That was a great help. Cheers.