If the OP was adding formulas line by line and the calculation was set to
AUTOMATIC, it would calculate each formula as it was entered. Sometimes I
wonder if it recalculates the whole sheet. With 20000 lines, it's going to
take a while.
--
HTH,
Barb Reinhardt



"cht13er" wrote:

On Mar 27, 10:29 pm, Monk wrote:
Thanks for your help Barb and Chris. Works great now.



"Barb Reinhardt" wrote:
Try chaning ISBLANK to ISEMPTY. Sorry about that.
--
HTH,
Barb Reinhardt

"Monk" wrote:

Thanks Barb. I am getting a Complie Error: Sub or Function not defined
message on
isblank (cell). Can you please advise a solution to this?

"Barb Reinhardt" wrote:

I made a couple of tweaks. Try it out and see if it works any faster.

Sub test()
'place a subtotal in column(V) wherever column(U) has a blank cell
Dim lRow As Long
Dim cell As Range
Dim RowCount As Long

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

lRow = Range("u65536").End(xlUp).Row + 1
RowCount = 0
For Each cell In Range("u2:U" & lRow)
If isblank(cell) Then
cell.Offset(0, 1).FormulaR1C1 = "=SUM(R[" & -RowCount &
"]C[-1]:R[-1]C[-1])"
RowCount = 0
Else
RowCount = RowCount + 1
End If
Next cell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

--
HTH,
Barb Reinhardt

"Monk" wrote:

I am using the code below to place a sum subtotal of values in Column V
whenever there is blank cell in Column U. The macro provides the correct
result but as the sheet has over 20,000 entries the macro takes about 10
minutes to complete. Is there a more time efficient (or non-VBA) way to
provide the same result?

Thanks

Sub test()
'place a subtotal in column(V) wherever column(U) has a blank cell
lrow = Range("u65536").End(xlUp).Row + 1
For Each cell In Range("u2:U" & lrow)
If cell.Value = isblank Then
cell.Offset(0, 1).Formula = "=SUM(R[" & -RowCount & "]C[-1]:R[-1]C[-1])"
RowCount = 0
Else
RowCount = RowCount + 1
End If
Next
End Sub- Hide quoted text -

- Show quoted text -

Monk -
isblank worked for me ... I use "" most of the time....

But still - it shouldn't take 10 minutes. Is it down to a few seconds
now for you?

Chris