how to reduce execution time of my code?

[catchchatur]

Experts,

I am running my macro and its taking almost 5 minutes during execution
on my computer (2GB RAM). Is there any way by which I can reduce this
execution time? Here is the code...

With wbSource.Worksheets("Flowdate")
For j = 24 To 36 Step 4
row_price = 2315

For i = 0 To 364
peak_price = .Cells(row_price, j).Value
If peak_price = "" Then
Worksheets("Flowdate").Cells(row_price, j).Value =
Worksheets("Flowdate").Cells(row_price, j).Offset(0, 2).Value
End If

offpeak_price = .Cells(row_price, j + 1).Value
If offpeak_price = "" Then
Worksheets("Flowdate").Cells(row_price, j + 1).Value =
Worksheets("Flowdate").Cells(row_price, j + 1).Offset(0, 2).Value
End If
row_price = row_price + 1
Next
MsgBox "Calculation done"
Next
End With

 

[Bob Phillips]

This might be quicker

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

With wbSource.Worksheets("Flowdate")
For j = 24 To 36 Step 4
row_price = 2315

For i = 0 To 364
peak_price = .Cells(row_price, j).Value
If peak_price = "" Then
.Cells(row_price, j).Value = _
.Cells(row_price, j).Offset(0, 2).Value
End If

offpeak_price = .Cells(row_price, j + 1).Value
If offpeak_price = "" Then
.Cells(row_price, j + 1).Value = _
.Cells(row_price, j + 1).Offset(0, 2).Value
End If
row_price = row_price + 1
Next
MsgBox "Calculation done"
Next
End With

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Is there a lot of calculation going on with these results? The code you
posted should be near instant, but if calculations go off of this new data it
will take a while, you can try turning calculation of before running and
turning it back on at the end of the code, that should fix.

 

[Guest]

I notice that you use "With wbSource.Worksheets("Flowdate")" yet you still
define your ranges using Worksheets("Flowdate"). You could remove those
references to clean the code up a little.

After looking at your code, I would guess that the formulas on your
worksheet are causing this macro to run a long time. Since you could be
changing values up to 2184 times given the looping in your code,
recalculation can really slow you down.

Try wrapping your code with this:

Application.Calculation = xlManual
' your code here...
Application.Calculation = xlAutomatic

This will cause your macro to make its changes and NOT recalculate until it
is done. Also, consider reviewing your functions to make sure they are
optimized.

HTH,
Pflugs

 

[Guest]

That looks simple enough. Perhaps turning off calculation if you have a lot
of formulas

lcalc = Application.Calculation
Application.Calculation = xlManual
With wbSource.Worksheets("Flowdate")
For j = 24 To 36 Step 4
row_price = 2315

For i = 0 To 364
peak_price = .Cells(row_price, j).Value
If peak_price = "" Then
Worksheets("Flowdate").Cells(row_price, j).Value =
Worksheets("Flowdate").Cells(row_price, j).Offset(0, 2).Value
End If

offpeak_price = .Cells(row_price, j + 1).Value
If offpeak_price = "" Then
Worksheets("Flowdate").Cells(row_price, j + 1).Value =
Worksheets("Flowdate").Cells(row_price, j + 1).Offset(0, 2).Value
End If
row_price = row_price + 1
Next
Next
MsgBox "Calculation done"
Application.Calculation = lcalc
End With

This assumes you don't need to recalculate the sheet on each
iteration/change made by your code.

 

[Guest]

One other thought/possibility:

With wbSource.Worksheets("Flowdate")
.DisplayPageBreaks = False

.. . .

 

[catchchatur]

Bob,

Your 4 lines reduced my code's execution time to 15 seconds... Its
amazinng!!! I looked into "Help" to understand the usage of these
commands. The excel files I am handelling are of huge sizes and
frequent changes in the screen was increasing the execution time.
Both screenupdating and calculcation worked great for me. Probably I
need to add these in all my relevant macros.

Thanks buddy,
Cheers,
Harshit