Hi

I need to get this VBA to speed through n rows as quickly as possible, doing 20 rows is fine, 5000 rows takes about 18minutes, before adding the:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
&
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

it took 6.5hours to run through 43000 rows. I have automatic calculation set to manual at present as well.

Code currently in use as below:

-----------

Sub Calc()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim lRowLoop As Long, lLastRow As Long
lLastRow = ActiveSheet.Cells(Rows.Count, "Q").End(xlUp).Row
For lRowLoop = 2 To lLastRow
ActiveCell.GoalSeek Goal:=1, ChangingCell:=ActiveCell.Offset(0, -1)
ActiveCell.Offset(1, 0).Select
Next lRowLoop
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Please help

Hi

You can try

Can speedup a bit as no selection is involved

Cheers
Shasur

__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)

Hi Shasur,

Thanks for the reply, I edited the code to read:

Sub Rhys()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim lRowLoop As Long, lLastRow As Long
lLastRow = ActiveSheet.Cells(Rows.Count, "Q").End(xlUp).Row
For lRowLoop = 2 To lLastRow
Cells(lRowLoop, 2).GoalSeek Goal:=1, ChangingCell:=Cells(lRowLoop, 1)
Next lRowLoop
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

But now I'm getting a 1004 runtime error on it, any ideas?

Hi

Can you try with

It might be due to the wrong column numbers ..

Cheers
Shasur

__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)