# Calculating subtotals

G

#### gary

(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?

J

#### James Ravenswood

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

D

#### Don Guillett

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

G

#### gary

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.

G

#### gary

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.

G

#### gary

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!!!!!!

D

#### Don Guillett

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

M

#### merjet

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

M

#### merjet

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

G

#### gary

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.

D

#### Don Guillett

I've sent my spreadsheet to Don.

Didn't get it. did you send to dguillett1 @gmail.com

G

#### gary

Hi Don,

The attachment on my first and second e-mails may have been too large
so I just sent you a much smaller one.

Gary

G

#### gary

If anyone can help me, I can e-mail a portion of my spreadsheet to
you.

D

#### Don Guillett

If anyone can help me, I can e-mail a portion of my spreadsheet to
you.
================
On 40000 rows, using subtotal took 245 seconds and this took less than
2 seconds.

Option Explicit
Sub DoSubtotalsforEachBlankSAS()
Dim st As Long
Dim lr As Long
Dim r As Long
Dim br As Long
'========
st = Timer ' start timing
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
r = 2
doit:
'MsgBox r
br = Columns(2).Find(What:="", After:=Cells(r, 2), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext).Row - 1
'MsgBox br
With Range("b" & br + 1 & ":f" & br + 1)
.Formula = "=sum(b" & r & ":b" & br & ")" ' sum 5 columns
.Value = .Value ' convert to values
End With
r = br + 2
If br > lr Then
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox Format(Timer - st, "0.00 \s\ec")
Exit Sub
End If
GoTo doit
End Sub

G

#### gary

================
On 40000 rows, using subtotal took 245 seconds and this took less than
2 seconds.

Option Explicit
Sub DoSubtotalsforEachBlankSAS()
Dim st As Long
Dim lr As Long
Dim r As Long
Dim br As Long
'========
st = Timer ' start timing
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
r = 2
doit:
'MsgBox r
br = Columns(2).Find(What:="", After:=Cells(r, 2), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext).Row - 1
'MsgBox br
With Range("b" & br + 1 & ":f" & br + 1)
.Formula = "=sum(b" & r & ":b" & br & ")" ' sum 5 columns
.Value = .Value   ' convert to values
End With
r = br + 2
If br > lr Then
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox Format(Timer - st, "0.00 \s\ec")
Exit Sub
End If
GoTo doit
End Sub

Hi Don,

Thank you very much. It's blazingly fast. How can your macro be
changed so the "keys' are on the row as the totals.

G

#### gary

================
On 40000 rows, using subtotal took 245 seconds and this took less than
2 seconds.

Option Explicit
Sub DoSubtotalsforEachBlankSAS()
Dim st As Long
Dim lr As Long
Dim r As Long
Dim br As Long
'========
st = Timer ' start timing
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
r = 2
doit:
'MsgBox r
br = Columns(2).Find(What:="", After:=Cells(r, 2), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext).Row - 1
'MsgBox br
With Range("b" & br + 1 & ":f" & br + 1)
.Formula = "=sum(b" & r & ":b" & br & ")" ' sum 5 columns
.Value = .Value   ' convert to values
End With
r = br + 2
If br > lr Then
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox Format(Timer - st, "0.00 \s\ec")
Exit Sub
End If
GoTo doit
End Sub

Hi Don,

Thank you very much. It's blazingly fast. Can your macro be
changed so the "keys' and "total" are on the rows as the totals?.

Gary

D

#### Don Guillett

Hi Don,

Thank you very much.  It's blazingly fast.  Can your macro be
changed so the "keys' and "total" are on the rows as the totals?.

Gary

'MsgBox br
Cells(br + 1, 1) = "          Keys Total" ' add this line
With Range("b" & br + 1 & ":f" & br + 1)

G

#### gary

'MsgBox br
Cells(br + 1, 1) = "          Keys Total"  ' add this line
With Range("b" & br + 1 & ":f" & br + 1)- Hide quoted text -

- Show quoted text -

Hi Don,

Gary

G

#### gary

Where do I "just add a line" in Don's macro?

Gary

G

#### gary

You didn't tell me where in your macro to "just add a line" so I tried
putting in various places but, each time, I got a compile error when
running the macro.

50,000 rows) and used Excel's Subtotal command (in the Outline group
on the Data tab). I started it on the first spreadsheet at 2 PM.
Immediatly after each spreadsheet was subtotaled, I started the next
one. By 1 AM, 9 of the spreadsheets had been subtotaled. I then
started the subtotal process on the last spreadsheet and went to bed.
I guess it finished about 2:30 AM. It took about 12 1/2 hours to get