VBA Code and Summing Next Non-Blank Cell in Column

M

Monk

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
 
C

cht13er

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


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
 
M

Monk

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


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

Barb Reinhardt

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
 
M

Monk

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

Barb Reinhardt

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



Monk said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
M

Monk

Thanks for your help Barb and Chris. Works great now.

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



Monk said:
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 said:
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



:

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

cht13er

Thanks for your help Barb and Chris. Works great now.



Barb Reinhardt said:
Try chaning ISBLANK to ISEMPTY.   Sorry about that.
Click to expand...
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?
:
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
:
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 -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...

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
 
B

Barb Reinhardt

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 said:
Thanks for your help Barb and Chris. Works great now.



Barb Reinhardt said:
Try chaning ISBLANK to ISEMPTY. Sorry about that.
Click to expand...
"Monk" wrote:
Click to expand...
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?
Click to expand...
"Barb Reinhardt" wrote:
Click to expand...
I made a couple of tweaks. Try it out and see if it works any faster.
Click to expand...
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
Click to expand...
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Click to expand...
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
Click to expand...
End Sub
Click to expand...
"Monk" wrote:
Click to expand...
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
Click to expand...

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

- Show quoted text -
Click to expand...

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

cht13er

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 goingto
take a while.  
--
HTH,
Barb Reinhardt



cht13er said:
Thanks for your help Barb and Chris. Works great now.
:
Try chaning ISBLANK to ISEMPTY.   Sorry about that.
--
HTH,
Barb Reinhardt
:
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?
:
I made a couple of tweaks.  Try it out and see if it works anyfaster.
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
:
I am using the code below to place a sum subtotal of values inColumn 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 -
Click to expand...
Click to expand...
Monk -
isblank worked for me ... I use "" most of the time....
Click to expand...
But still - it shouldn't take 10 minutes. Is it down to a few seconds
now for you?
Click to expand...
Chris- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

OFFSET LOOKUP function in VBA? 9
how to: c.value = "a cell value" 5
need SUM code help 2
Blank cells problem 5
VBA CreateNames (ignores last 5 rows?) 6
Efficiency of Code Help 4
Compare strings and return difference and position 1
Concat a three column range into one cell Then Next... 2

Top