Solved: Macros and Formulas

[austenr]

I have a curious problem. I have a worksheet full of formulas and conditional formatting. I am trying to run a macro that checks a column and replaces blanks with a value. The problem is it takes forever to run. Do formulas and conditional formatting slow down macro execution? I tested the macro with 20 cells of test data and it took about 30 seconds to complete. Thanks :dunno

[Bob Phillips]

I have a curious problem. I have a worksheet full of formulas and conditional formatting. I am trying to run a macro that checks a column and replaces blanks with a value. The problem is it takes forever to run. Do formulas and conditional formatting slow down macro execution? I tested the macro with 20 cells of test data and it took about 30 seconds to complete. Thanks :dunno

What is probably happeing is that each time you change a value, it forces a sheet recalculation, and a screen re-paint.

Try preceding the code with


Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


and reset at the end with


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

[austenr]

Thanks that got it. http://vbaexpress.com/forum/images/smilies/023.gif