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]

One of the reasons your code is so slow is because of all the SELECT statements. They are usually not necessary, and it slows down the code.
Usually, whenever you have one line of code that ends is "Select" and the next line begins with "ActiveCell" or "Selection", those lines can be combined.

Also, disabling screen updating, preventing events from firing, and delaying calculations until the end should also help speed it up.

Try this version:

Sub Êáèáñéóìüò_íÝï()
'
' Êáèáñéóìüò_íÝï Macro
'
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    Range("U2:X3").ClearContents
    Range("AA2:AA8").ClearContents
    Range("M17:W18").ClearContents
    Range("H14:K14").FormulaR1C1 = "- - - - - - -"
    Range("H15:K15").FormulaR1C1 = "- - - - - - -"
    Range("H16:K16").FormulaR1C1 = "- - - - - - -"
    Range("H17:K17").FormulaR1C1 = "- - - - - - -"
    Range("G18").ClearContents
    Range("I18").ClearContents
    Range("K18").ClearContents
    Range("T14:W14").FormulaR1C1 = "- - - - - - -"
    Range("T15:W15").FormulaR1C1 = "- - - - - - -"
    Range("G21:J21").FormulaR1C1 = "- - - - - - -"
    Range("G22:J22").FormulaR1C1 = "- - - - - - -"
    Range("G23:J23").FormulaR1C1 = "- - - - - - -"
    Range("G25:J25").FormulaR1C1 = "- - - - - - -"
    Range("U21:X21").FormulaR1C1 = "- - - - - - -"
    Range("U22:X22").FormulaR1C1 = "- - - - - - -"
    Range("U23:X23").FormulaR1C1 = "- - - - - - -"
    Range("U24:X24").FormulaR1C1 = "- - - - - - -"
    Range("U25:X25").FormulaR1C1 = "- - - - - - -"
    Range("U26").ClearContents
    Range("W28").ClearContents
    Range("W30").ClearContents
    Range("K30").ClearContents
    Range("C33").ClearContents
    Range("C34").ClearContents
    Range("C35").ClearContents
    Range("G33:G35").ClearContents
    Range("V33:V34").ClearContents
    Range("V37").ClearContents
    Range("I37").ClearContents
    Range("D28").FormulaR1C1 = "×"
    Range("H40:H41").ClearContents
    Range("U40:U41").ClearContents
    Range("A46:X47").ClearContents

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

End Sub

 

[footoo]

Could also reduce your code to 3 lines like this:

Range("D28")="x"
Range("H14:K17,T14:W15,G21:J23,G25:J25,U21:X25") = "- - - - - - -"

And similarly for ClearContents.

 

[Lux Aeterna]

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?

 

[Joe4]

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

If you have a lot of formulas, especially ones referencing other sheet, that could cause it to be slow as it is recalculating everything.

If you do what I did here and add these three lines:

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

to the beginning of each Macro, and then add these lines:

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

to the end, that should speed up those other ones too (as well as taking my advice and trying to get rid of any unnecessary "Select" lines that you may have in those macros too).

Just be careful that if any of your Macros have some sort of "Exit Sub" statement before the end, you will need to make sure that those last three lines get run first, i.e.
if you have this line in the middle of your code somewhere:

    Exit Sub

you should update it to:

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

Note that is "Exit Sub", not the "End Sub" that you have at the very end of your code marking the end of the macro/procedure.
The "Exit Sub" line is used to bail out of a macro before the end.

 

[Lux Aeterna]

Thanks for the useful advice!

There's just a VLOOKUP formula in the Results sheet, referring to the List sheets. The Stats sheet has several formulas referring to the List sheets. However, Stats sheet is independent from Results sheet and that makes it awkward. Could I somehow make Stats sheet calculations on demand?

I also have data validation on the List sheets that refer to the Stats sheet. Could that one have caused the problem?

 

[Joe4]

There's just a VLOOKUP formula in the Results sheet, referring to the List sheets. The Stats sheet has several formulas referring to the List sheets. However, Stats sheet is independent from Results sheet and that makes it awkward. Could I somehow make Stats sheet calculations on demand?

You could set calculations to manual, and see if that makes any difference, though I doubt it will if you add what I recommended to each macro, because that is precisely what this line does:

Application.Calculation = xlCalculationManual

See here more details on doing that manually: How to Manually Calculate Only the Active Worksheet in Excel

I also have data validation on the List sheets that refer to the Stats sheet. Could that one have caused the problem?

I don't think so.

Were the other macros still slow after you made the additions I suggested?
If so, can you post the VBA code of one of those slow macros?

 

[Lux Aeterna]

I haven't adjusted the other macros, because the lag is really small (it won't take wore than 1/2 sec) and it's not worth editing all those macros. I'll keep that in mind, though!

Where in the Stats sheet should I put that?

VBA Code:
Application.Calculation = xlCalculationManual

 

[Joe4]

What I gave you is VBA code and only goes in macros/VBA procedures, and it applies to the entire workbook, not just one sheet.

If you take a look at that link I gave you, it shows you how to manaully change the calculation mode to manual.

I haven't adjusted the other macros, because the lag is really small (it won't take wore than 1/2 sec) and it's not worth editing all those macros. I'll keep that in mind, though!

So what exactly is it that is still slow?

 

[Lux Aeterna]

So what exactly is it that is still slow?

It was just the macro you edited that was slow. The rest aren't slow, just slightly slower than they used to be.

I'll check the link you gave me. I'm sure it will come in handy!