Slow Calculating Processor...

[JonElCanche]

If I add a row, delete some inputs, update something, run a macro, etc. I have to wait for about a minute while I believe every array and formula is recalculating. Is there anyway to speed this up? Or at least turn off the calculation when I run the macro? Thanks!

 

[Aladin Akyurek]

If I add a row, delete some inputs, update something, run a macro, etc. I have to wait for about a minute while I believe every array and formula is recalculating. Is there anyway to speed this up? Or at least turn off the calculation when I run the macro? Thanks!

If the macro is not the main reason for the slow performance, try to avoid or rewrite formulas with volatile functions like Indirect, Offset, etc. Also, try to simplify or rewrite array or Sumproduct formulas that you might have in large numbers.

 

[JonElCanche]

If the macro is not the main reason for the slow performance, try to avoid or rewrite formulas with volatile functions like Indirect, Offset, etc. Also, try to simplify or rewrite array or Sumproduct formulas that you might have in large numbers.

I think I can handle the slow recalculation with everything but when the macro runs. I have tried to use
Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic
But it does not seem to be working. Is that the correct code or is there something else I can do?

 

[Peter_SSs]

Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic

Provided the rest of your macro is between these two lines, that should stop calculation while the macro runs. Of course, when the macro processes the last of these lines, if a calculation is required then that calculation will then be performed so you may still have a wait.

 

[baitmaster]

adding and deleting rows or columns can also slow things down. Switch off screenupdating, and the animation of these features (i.e. how it takes a moment to visibly move the row into place)

Still won't change the fact that all calculations will need to update when you switch calculations back on...

 

[JonElCanche]

This is my full macro and it takes 53 seconds to run. Any ideas why?



Sub Macro1()

Application.ScreenUpdating = False


'
' Macro1 Macro
'

'
Range("D7:H7").Select
Selection.Copy
Sheets("Maintainance").Select
Range("B1").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B18").Select
Sheets("Update").Select
Range("D7:H7").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("D7").Select


Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

This is my full macro and it takes 53 seconds to run. Any ideas why?



Sub Macro1()

Application.ScreenUpdating = False


'
' Macro1 Macro
'

'
Range("D7:H7").Select
Selection.Copy
Sheets("Maintainance").Select
Range("B1").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B18").Select
Sheets("Update").Select
Range("D7:H7").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("D7").Select


Application.ScreenUpdating = True
End Sub

When posting code, please use code tags - see my signature block for how to do that. Reading and de-bugging code is much easier when formatted appropriately.

I don't know why your code takes 53 seconds to run but it could easily relate to calculation time. You had discussed setting calculation to manual while the code runs but I don't see that in your code.

Selecting things in vba slows your code so you could remove that. However, there isn't enough of that in your code to make that much difference for just transferring a small set of values to another sheet.

Here is some suggested code to try. I have removed all the selecting and added the 'Calculation' lines. I have also assumed that the original D7:H7 range being copied is on the 'Update' sheet. If that is not the case a code tweak would be required.

See if it makes any difference.

Sub Macro1()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    With Sheets("Update").Range("D7:H7")
        Sheets("Maintainance").Range("B1").End(xlDown) _
            .Offset(1).Resize(, .Columns.Count).Value = .Value
        .ClearContents
    End With
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub