Calculating subtotals

  • Thread starter Thread starter gary
  • Start date Start date
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?
 
Do you want the subtotals inserted in the empty rows in the
appropriate columns??
 
Do you want the subtotals inserted in the empty rows in the
appropriate columns??
Click to expand...

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.
 
Do you want the subtotals inserted in the empty rows in the
appropriate columns??
Click to expand...

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.
 
Do you want the subtotals inserted in the empty rows in the
appropriate columns??
Click to expand...

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!!!!!!
 
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!!!!!!
Click to expand...

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
 
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
Click to expand...

I've sent my spreadsheet to Don.
 
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
 
If anyone can help me, I can e-mail a portion of my spreadsheet to
you.
Click to expand...
================
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
 
================
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
Click to expand...

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.
 
================
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
Click to expand...

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
 
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
Click to expand...

Sure, Just add a line

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

'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 -
Click to expand...



Hi Don,

Where do I "just add a line" in your macro?

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.

I then split my spreadsheet into 10 smaller spreadsheets (each with
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
subtotals on all the spreadsheets!!!!!
 
Back
Top