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

On Mar 27, 6:09 pm, 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


I ran the code you provided and it took less than 2 mississippi's to
run for 20,000 rows ....

If I were you I would go through the code step by step and make sure
it's not calling up other subs as it runs, etc...

HTH

Chris

Thanks Chris. There are no other subs but I note that there are formulas in
Column U which provide the "" result for a cell. Therefore I suppose the
cell.value = isblank code is incorrect. Do you think that would cause the
excessive time for calculation?

"cht13er" wrote:

On Mar 27, 6:09 pm, 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


I ran the code you provided and it took less than 2 mississippi's to
run for 20,000 rows ....

If I were you I would go through the code step by step and make sure
it's not calling up other subs as it runs, etc...

HTH

Chris

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

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

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

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

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

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

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