Macro runs very slow

[Jeff2554]

Can anyone tell me why this macro takes over a minute to execute?
Or perhaps offer an alternative way to do these commands?

Sub Clear()
'
' Clear Macro
'
Application.EnableEvents = False
'Move Stock
    Range("NHKStock").Copy ThisWorkbook.Sheets("Lanes").Range("AI4")

'Move Leftmost Lane
    Range("NHKLeftmost").Copy ThisWorkbook.Sheets("Lanes").Range("AF3")

'Reformat
    Range("AllNHKLanes").Copy ThisWorkbook.Sheets("Lanes").Range("E3")

'Change next load number
    ThisWorkbook.Sheets("Lanes").Range("AC4") = ThisWorkbook.Sheets("Lanes").Range("Z4") + 1
    
'Clear Current from moved lane
    ThisWorkbook.Sheets("Lanes").Range("AD7:AD37").ClearContents
    
'Clear Load from Pullsheet
    ThisWorkbook.Sheets("Pullsheet Drop").Range("D1:D34").Delete
    
Application.EnableEvents = True
    
End Sub

 

[kevin9999]

Could you provide a copy of your actual file via Google Drive, Dropbox or similar file sharing platform?

 

[Jeff2554]

Could you provide a copy of your actual file via Google Drive, Dropbox or similar file sharing platform?

Yep, here you go. https://docs.google.com/spreadsheet...ouid=118310622381152064432&rtpof=true&sd=true

 

[kevin9999]

Access denied. You need to make the file available to anyone with the link.

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Macro runs very slow

Can anyone tell me why this macro takes over a minute to execute? Or perhaps offer an alternative way to do these commands? Sub Clear() ' ' Clear Macro ' Application.EnableEvents = False 'Move Stock Range("NHKStock").Copy ThisWorkbook.Sheets("Lanes").Range("AI4")

www.excelforum.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Alex Blakenburg]

I am not a big fan of conditional formatting, it is volatile and will kill your spreadsheet performance which is what is happening to you.
The first image below is what your formatting looks like in the sheet Lanes in a "single" cell.
It is so bad I can't even delete the formatting without sending your spreadsheet into a spin.
PS: I would encourage you to get rid of the Worksheet SelectionChange event which is not helping performance but is not the main issue.

What I did to remove the conditional formatting was:

• Change the name of the sheet Lanes (I just put XX - keep it short - it will make a later step easier)
• Create a new blank sheet and called it Lanes
• Copy the data range of sheet XX
• Paste Special Formulas into A1 of the newly created sheet Lanes
• Now to change the Named Ranges
  • Ctrl+F3 Open the Names manager
  • Edit each of the Names and change the Refers to from using XX to using Lanes
• Delete the sheet XX
• Save the workbook (maybe to a new name )
• Now put back essential formatting.

You can try to put back the conditional formatting if you have to have it. Yours seems to have gone off the rails and has been replicated many possibly 100s of times on each cell.

Your conditional formatting looks like this (don't even try to scroll down it will lock up your excel)

What it should look like

 

[Jeff2554]

Thank you Alex! That was definitely the issue and it is working great now.

 

[Alex Blakenburg]

Thanks for the confirmation. I was curious as to what could have caused the conditional formatting to replicate to that level, and it looks like the mostly cause would be the use of the option "Paste Special > All Merging Conditional Formats". I would have thought of it as being something that would prevent the issue from arising but instead seems to be appending rules which to mind is quite different to a merging of rules.