On Apr 1, 10:28*am, Barb Reinhardt
wrote:
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- Hide quoted text -

- Show quoted text -

Barb - I realized sometime in the middle of some night that that must
have been what was happening - so just using
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
should make everything quicker :)

Thanks :)

Chris