On Feb 13, 9:07*am, Don Guillett wrote:
On Feb 12, 8:34*pm, gary wrote: 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

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