(I'm using Excel 2007)

My spreadsheet has column labels: key amt1 amt2 amt3
amt4 amt5

At each change in "key", I need the sums of "amt1", "amt2", "amt3",
"amt4" "amt5".

My speadsheet contains 232846 rows with amounts.
Between each "key", there's an empty row (i.e., 262062 empty rows)

When I use the Subtotal command in the Outline group on the Data tab,
it takes more than 10 HOURS to calculate the subtotals and grand
totals.

Is there a faster way to compute the subtotals and grand totals?

Do you want the subtotals inserted in the empty rows in the
appropriate columns??

On Feb 12, 3:35*am, James Ravenswood
wrote:
Do you want the subtotals inserted in the empty rows in the
appropriate columns??

After running for 10 1/2 hours, the "progress bar indicator" (below
the spreadsheet) has moved only 1/4 of the way acoss the box. Since
I can't wait 42 hours for Excel to finish, I pressed Esc and found
that the last cell containing subtotals is 298,202.

In my spreadsheet, some "keys" exist twice. The empty row ensures
that I get subtotals for both "keys" (even though the subtotals will
be the same). It doesn't matter if the subtotals are inserted in the
empty rows or in new rows as long as I get subtotals whenever the
"key" changes.

I'm desperate for a solution; I've been working on this project now
for 4 weekends and the project is due in two days.

On Feb 12, 3:35*am, James Ravenswood
wrote:
Do you want the subtotals inserted in the empty rows in the
appropriate columns??

After running for 10 1/2 hours, the progress indicator (below the
spreadsheet) has moved only 1/4 of the way across the bar. At that
rate, it'll take 42 hours to finish. Since I can't wait that long, I
pressed Esc. Subtotals have been inserted only down to row 304000.

Because some of the "keys" exist twice, the empty rows ensure that
subtotals are computed for both keys (although the subtotals will be
the same). It doesn't matter it the subtotals are put inthe empty
rows or in new rows, as long as I get subtotals whenever the "key"
changes).

I'm getting desperate for a solution. I've been working on this for 4
weekends and the project is due in two days!!!!!!

On Feb 12, 9:18*am, gary wrote:
On Feb 12, 3:35*am, James Ravenswood
wrote:

Do you want the subtotals inserted in the empty rows in the
appropriate columns??

After running for 10 1/2 hours, the progress indicator (below the
spreadsheet) has moved only 1/4 of the way across the bar. *At that
rate, it'll take 42 hours to finish. *Since I can't wait that long, I
pressed Esc. Subtotals have been inserted only down to row 304000.

Because some of the "keys" exist twice, the empty rows ensure that
subtotals are computed for both keys (although the subtotals will be
the same). * It doesn't matter it the subtotals are put inthe empty
rows or in new rows, as long as I get subtotals whenever the "key"
changes).

I'm getting desperate for a solution. I've been working on this for 4
weekends and the project is due in two days!!!!!!

If desired, send me a file with most of the rows deleted.
dguillett1 @gmail.com

Try removing the SUBTOTAL functions from the worksheet and do them by
macro.
For example, instead of having the formula =SUBTOTAL(9,B2:B4) in cell
B5 and similar
in C5 and D5, use

Sub MySubtotals()
For iCol = 2 to 4
Range("B5") = Application.Sum("B2:B4)

End Sub

Oops. I accidentally posted before finishing. Make that:

Sub MySubtotals()
For iCol = 2 To 4
Cells(5, iCol) = Application.Sum(Range(Cells(2, iCol),
Cells(4, iCol)))
Next iCol
End Sub

On Feb 12, 8:21*am, merjet wrote:
Oops. I accidentally posted before finishing. Make that:

Sub MySubtotals()
* * For iCol = 2 To 4
* * * * Cells(5, iCol) = Application.Sum(Range(Cells(2, iCol),
Cells(4, iCol)))
* * Next iCol
End Sub

I've sent my spreadsheet to Don.

On Feb 12, 3:35*am, James Ravenswood
wrote:
Do you want the subtotals inserted in the empty rows in the
appropriate columns??

After running for 10 1/2 hours, the "progress bar indicator" (below
the spreadsheet) has moved only 1/4 of the way acoss the box. Since
I can't wait 42 hours for Excel to finish, I pressed Esc and found
that the last row containing subtotals is 298,202.

In my spreadsheet, some "keys" exist twice. The empty row ensures
that I get subtotals for both "keys" (even though the subtotals will
be the same). It doesn't matter if the subtotals are inserted in the
empty rows or in new rows as long as I get subtotals whenever the
"key" changes.


I'm desperate for a solution; I've been working on this project now
for 4 weekends and the project is due in two days.

Haven't tried this but try turning calculation off first.
Maybe a FINDNEXT

On Feb 11, 10:17*pm, gary wrote:
(I'm using Excel 2007)

My spreadsheet has column labels: *key * *amt1 * *amt2 * *amt3
amt4 * *amt5

At each change in "key", I need the sums of "amt1", "amt2", "amt3",
"amt4" * *"amt5".

My speadsheet contains 232846 rows with amounts.
Between each "key", there's an empty row (i.e., 262062 empty rows)

When I use the Subtotal command in the Outline group on the Data tab,
it takes more than 10 HOURS to calculate the subtotals and grand
totals.

Is there a faster way to compute the subtotals and grand totals?