Macro slowing down because of an extra sheet

[Lux Aeterna]

Not sure if this is the right place to ask.

I've got the following macro that I run on demand on a specific sheet named "results".

Sub Êáèáñéóìüò_íÝï()
'
' Êáèáñéóìüò_íÝï Macro
'

'
    Range("U2:X3").Select
    Selection.ClearContents
    Range("AA2:AA8").Select
    Selection.ClearContents
    Range("M17:W18").Select
    Selection.ClearContents
    Range("H14:K14").Select
    ActiveCell.FormulaR1C1 = "- - - - - - -"
    Range("H15:K15").Select
    ActiveCell.FormulaR1C1 = "- - - - - - -"
    Range("H16:K16").Select
    ActiveCell.FormulaR1C1 = "- - - - - - -"
    Range("H17:K17").Select
    ActiveCell.FormulaR1C1 = "- - - - - - -"
    Range("G18").Select
    Selection.ClearContents
    Range("I18").Select
    Selection.ClearContents
    Range("K18").Select
    Selection.ClearContents
    Range("T14:W14").Select
    ActiveCell.FormulaR1C1 = "- - - - - - -"
    Range("T15:W15").Select
    ActiveCell.FormulaR1C1 = "- - - - - - -"
    Range("M16:W16").Select
    Range("G21:J21").Select
    ActiveCell.FormulaR1C1 = "- - - - - - -"
    Range("G22:J22").Select
    ActiveCell.FormulaR1C1 = "- - - - - - -"
    Range("G23:J23").Select
    ActiveCell.FormulaR1C1 = "- - - - - - -"
    Range("G25:J25").Select
    ActiveCell.FormulaR1C1 = "- - - - - - -"
    Range("U21:X21").Select
    ActiveCell.FormulaR1C1 = "- - - - - - -"
    Range("U22:X22").Select
    ActiveCell.FormulaR1C1 = "- - - - - - -"
    Range("U23:X23").Select
    ActiveCell.FormulaR1C1 = "- - - - - - -"
    Range("U24:X24").Select
    ActiveCell.FormulaR1C1 = "- - - - - - -"
    Range("U25:X25").Select
    ActiveCell.FormulaR1C1 = "- - - - - - -"
    Range("U26").Select
    Range("L28").Select
    Selection.ClearContents
    Range("W28").Select
    Selection.ClearContents
    Range("W30").Select
    Selection.ClearContents
    Range("K30").Select
    Selection.ClearContents
    Range("C33").Select
    Selection.ClearContents
    Range("C34").Select
    Selection.ClearContents
    Range("C35").Select
    Selection.ClearContents
    Range("G33:G35").Select
    Selection.ClearContents
    Range("V33:V34").Select
    Selection.ClearContents
    Range("V37").Select
    Selection.ClearContents
    Range("I37").Select
    Selection.ClearContents
    Range("D28").Select
    ActiveCell.FormulaR1C1 = "×"
    Range("D29").Select
    Range("H40:H41").Select
    Selection.ClearContents
    Range("U40:U41").Select
    Selection.ClearContents
    Range("A46:X47").Select
    Selection.ClearContents
    Range("A46:X47").Select
    Range("U2:X2").Select
End Sub

In the past couple of weeks that macro runs extremely slow.

I experimented a bit and realised that the macro is slowed down by another sheet on the workbook named "stats". Once I delete the stats sheet, the macro runs fast again.

It's unexpected though because results and stats sheet aren't directly linked to each other.

There's an indirect link between them though. Results sheet is linked to List sheet (through VLOOKUP) and List sheet is linked to Stat's sheet through various formulas.

It seems that some of the adjustments I've made to the stats sheet caused the problem -no idea which- but as I said this slow down is unjustified to my mind.

Any idea how to save the day?

 

[Joe4]

How slow is the Macro after the edits I gave you?

 

[Lux Aeterna]

It lasts 1-2 seconds. My original macro used to last a bit longer, and when the problem appeared it'd last 15-20 seconds, causing the workbook not to respond.

 

[Joe4]

It lasts 1-2 seconds. My original macro used to last a bit longer, and when the problem appeared it'd last 15-20 seconds, causing the workbook not to respond.

I am confused. It would appear that all issues of slowness with this workbook have been addressed and everything is good now, right?
So then why are we still talking about making other changes to calculation modes?

 

[Lux Aeterna]

Yes, all issues are good now!

I was worried that whatever effect the Stats sheet had on the Macro you edited might come up again. So I was thinking of making my workbook lighter

 

[Joe4]

Yes, all issues are good now!

I was worried that whatever effect the Stats sheet had on the Macro you edited might come up again. So I was thinking of making my workbook lighter

OK, it shouldn't affect the current code at all, as long as you saved the updates I gave you.
And if you have more code to add in the future, if you follow the advice I gave you, that should handle that on your new code too.

Those tips I gave you can be applied to most VBA procedures/macros. They are general tips for speeding up your code.

 

[footoo]

Wasn't sure how to write ClearContents on the 3-line code, but your first code does the job perfectly fine!

I also added

    Range("U2:X2").Select
    ActiveWindow.ScrollRow = 1
    ActiveWindow.ScrollColumn = 1

before your

    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic

You saved the day!

PS. I'm noticing that all macros on this sheet are a bit slower. How could another unrelated sheet affect that?

With Application
.Calculation = xlCalculationManual
.EnableEvents = False
.ScreenUpdating = False

Range("D28") = "x"
Range("H14:K17,T14:W15,G21:J23,G25:J25,U21:X25") = "- - - - - - -"
Range("A46:X47,C33:C35,G18,G33:G35,H40:H41,I18,I37,K18,K30," _
    & "M17:W18,U2:X3,U26,U40:U41,V33:V34,V37,W28,W30").ClearContents
Range("U2:X2").Select
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1

.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With

 

[Lux Aeterna]

With Application
.Calculation = xlCalculationManual
.EnableEvents = False
.ScreenUpdating = False

Range("D28") = "x"
Range("H14:K17,T14:W15,G21:J23,G25:J25,U21:X25") = "- - - - - - -"
Range("A46:X47,C33:C35,G18,G33:G35,H40:H41,I18,I37,K18,K30," _
    & "M17:W18,U2:X3,U26,U40:U41,V33:V34,V37,W28,W30").ClearContents
Range("U2:X2").Select
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1

.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With

Thanks a lot, Footoo!