slow after running macro

%3CLINGO-SUB%20id%3D%22lingo-sub-217613%22%20slang%3D%22en-US%22%3Eslow%20after%20running%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-217613%22%20slang%3D%22en-US%22%3E%3CP%3EI%20run%20a%20macro%20in%20excel%20using%20VB.%26nbsp%3B%20It%20runs%20extremely%20slow.%26nbsp%3B%20Once%20the%20program%20ends%2C%20the%20workbook%20is%20very%20slow%20to%20respond%20in%20normal%20worksheet%20movements.%26nbsp%3B%20if%20I%20close%20the%20program%20and%20reopen%2C%20it%20responds%20normally.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-217613%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-219090%22%20slang%3D%22en-US%22%3ERe%3A%20slow%20after%20running%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-219090%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3EThe%20idea%20behind%20these%20codes%20is%20to%20turn%20off%20everything%20that%20may%20lead%20to%20slow%20down%20the%20macro%20and%20not%20necessary%20for%20its%20operation.%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20fact%2C%20this%20is%20the%20first%20time%20I%20use%20these%20codes%20in%20separate%20Subs%20and%20then%20call%20it%20inside%20the%20main%20Sub.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22short_text%22%3E%3CSPAN%20class%3D%22%22%3EI%20took%20this%20idea%20from%20this%20%3CA%20href%3D%22https%3A%2F%2Fwww.thespreadsheetguru.com%2Fblog%2F2015%2F2%2F25%2Fbest-way-to-improve-vba-macro-performance-and-prevent-slow-code-execution%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Esite%3C%2FA%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%2C%20please%20check%20it%20out%20to%20see%20the%20full%20explanation%20of%20these%20codes.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-219031%22%20slang%3D%22en-US%22%3ERe%3A%20slow%20after%20running%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-219031%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Haytham%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20so%20much.%26nbsp%3B%20It%20runs%20much%20faster.%26nbsp%3B%20I%20saw%20you%20added%20some%20code%20at%20the%20front%20of%20my%20macro%20-%20one%20of%20which%20doesn't%20allow%20the%20macro%20to%20update%20the%20screen%20which%20I%20could%20see%20takes%20a%20huge%20amount%20of%20time.%26nbsp%3B%20I'm%20not%20sure%20what%20the%20others%20do%2C%20but%20if%20you%20could%20point%20me%20to%20where%20I%20could%20find%20out%2C%20I'd%20appreciate%20it.%26nbsp%3B%20I%20need%20the%20conditional%20formatting%20so%20it%20is%20easy%20for%20the%20other%20golfers%20to%20visually%20see%20how%20they%20are%20doing%20and%20what%26nbsp%3Bholes%20they%20need%20to%20work%20on.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20have%20been%20a%20big%20help!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBruce%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-218919%22%20slang%3D%22en-US%22%3ERe%3A%20slow%20after%20running%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-218919%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Bruce%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%20on%20the%20late%20reply!%3C%2FP%3E%3CP%3EThe%20macro%20performs%20a%20very%20long%20operation%2C%20and%20includes%20several%20For%20Loops%20statements%2C%20this%20is%20why%20it%20takes%20too%20much%20time!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnother%20reason%2C%20there%20are%20too%20many%20Conditional%20Formatting%20rules%20in%20the%20worksheet!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20I've%20updated%20the%20macro%20to%20speed%20up%20the%20operation%2C%20please%20test%20it%20in%20the%20attached%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20please%20review%20Conditional%20Formatting%20rules%20in%20the%20worksheet%20and%20see%20if%20you%20really%20need%20them%20all.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-217935%22%20slang%3D%22en-US%22%3ERe%3A%20slow%20after%20running%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-217935%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Haytham%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20taking%20the%20time%20to%20look%20at%20it.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20a%20copy%20of%20the%20workbook%20(I%20have%20put%20random%20names%20in%20the%20name%20column%2C%20but%20that%20does%20not%20affect%20the%20macro).%26nbsp%3B%20I%20am%20not%20a%20sophisticated%20programmer%20and%20don%E2%80%99t%20know%20all%20the%20shortcuts%20in%20coding%2C%20but%20the%20macro%20does%20what%20I%20want%20it%20to%20do%2C%20it%20just%20takes%20too%20much%20time%2C%20and%20once%20it%20exits%2C%20the%20workbook%20is%20slow%20to%20navigate%20until%20I%20close%20it%20and%20reopen%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20macro%20does%20two%20basic%20procedures%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3Eit%20compares%20the%20raw%20data%20from%20golfers%20scores%2C%20computes%20the%20senior%20handicaps%20and%20compares%20it%20to%20the%20course%20handicap%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3Eit%20takes%20the%20individual%20raw%20data%2C%20sorts%20by%20name%2C%20and%20prepares%20a%20printable%20version%20for%20each%20player.%26nbsp%3B%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20see%20a%20fix%2C%20just%20send%20me%20a%20copy%20to%20the%20revised%20macro%20with%20any%20comments%20you%20want%20to%20make.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EBruce%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-217626%22%20slang%3D%22en-US%22%3ERe%3A%20slow%20after%20running%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-217626%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Bruce%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20need%20to%20see%20this%20macro%20to%20figure%20out%20what%20the%20problem%20is.%3C%2FP%3E%3CP%3EPlease%20copy%20the%20macro%20code%20here%20in%20a%20reply.%3C%2FP%3E%3CP%3EIt%20would%20be%20better%20if%20you%20can%20attach%20the%20workbook%20itself.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2166000%22%20slang%3D%22en-US%22%3ERe%3A%20slow%20after%20running%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2166000%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGood%20morning%2C%20I%20am%20having%20the%20same%20issue.%26nbsp%3B%20I%20am%20not%20a%20coder.%26nbsp%3B%20I%20grabbed%20a%20code%20off%20the%20internet%20that%20would%20allow%20me%20to%20sum%20by%20color.%26nbsp%3B%20Now%20the%20xlsm%20file%20is%20running%20extremely%20slow.%26nbsp%3B%20I've%20attached%20a%20copy%20of%20the%20excel.%26nbsp%3B%20Can%20you%20help%20me%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2166650%22%20slang%3D%22en-US%22%3ERe%3A%20slow%20after%20running%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2166650%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F978747%22%20target%3D%22_blank%22%3E%40TAntolik%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMostly%2C%20I%20would%20say%20it%20is%20due%20to%20the%20fact%20that%20you%20are%20passing%20entire%20columns%20(1%20million%2B%20cells)%20to%20the%20function%20to%20loop%20through.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20changed%20the%20functions%20to%20restrict%20the%20range%20it%20looks%20at%20to%20the%20used%20range%2C%20but%20sometimes%20the%20worksheet's%20used%20range%20needs%20to%20be%20reset%20so%20you%20may%20need%20to%20check%2Freset%20it%20if%20you%20notice%20it%20bogging%20down%20again.%20Or%2C%20just%20pass%20your%20actual%20range%20to%20the%20function%20instead%20of%20the%20entire%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Flocate-and-reset-the-last-cell-on-a-worksheet-c9e468a8-0fc3-4f69-8038-b3c1d86e99e9%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Flocate-and-reset-the-last-cell-on-a-worksheet-c9e468a8-0fc3-4f69-8038-b3c1d86e99e9%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I run a macro in excel using VB.  It runs extremely slow.  Once the program ends, the workbook is very slow to respond in normal worksheet movements.  if I close the program and reopen, it responds normally.

12 Replies

Hi Bruce,

 

We need to see this macro to figure out what the problem is.

Please copy the macro code here in a reply.

It would be better if you can attach the workbook itself.

 

Regards

Hi Haytham,

 

Thanks for taking the time to look at it. 

 

I have attached a copy of the workbook (I have put random names in the name column, but that does not affect the macro).  I am not a sophisticated programmer and don’t know all the shortcuts in coding, but the macro does what I want it to do, it just takes too much time, and once it exits, the workbook is slow to navigate until I close it and reopen it.

 

The macro does two basic procedures:

 

  1. it compares the raw data from golfers scores, computes the senior handicaps and compares it to the course handicap

 

  1. it takes the individual raw data, sorts by name, and prepares a printable version for each player. 

 

If you see a fix, just send me a copy to the revised macro with any comments you want to make.

 

 

Thanks,

Bruce

Hi Bruce,

 

Sorry on the late reply!

The macro performs a very long operation, and includes several For Loops statements, this is why it takes too much time!

 

Another reason, there are too many Conditional Formatting rules in the worksheet!

 

However, I've updated the macro to speed up the operation, please test it in the attached file.

 

Also, please review Conditional Formatting rules in the worksheet and see if you really need them all.

 

Regards

Hi Haytham,

 

Thanks so much.  It runs much faster.  I saw you added some code at the front of my macro - one of which doesn't allow the macro to update the screen which I could see takes a huge amount of time.  I'm not sure what the others do, but if you could point me to where I could find out, I'd appreciate it.  I need the conditional formatting so it is easy for the other golfers to visually see how they are doing and what holes they need to work on.

 

You have been a big help!

 

Bruce 

Thank you!

 

The idea behind these codes is to turn off everything that may lead to slow down the macro and not necessary for its operation.

 

In fact, this is the first time I use these codes in separate Subs and then call it inside the main Sub.

I took this idea from this site, please check it out to see the full explanation of these codes.

 

Regards

@Haytham Amairah 

 

Good morning, I am having the same issue.  I am not a coder.  I grabbed a code off the internet that would allow me to sum by color.  Now the xlsm file is running extremely slow.  I've attached a copy of the excel.  Can you help me?

@TAntolik 

 

Mostly, I would say it is due to the fact that you are passing entire columns (1 million+ cells) to the function to loop through.

 

I changed the functions to restrict the range it looks at to the used range, but sometimes the worksheet's used range needs to be reset so you may need to check/reset it if you notice it bogging down again. Or, just pass your actual range to the function instead of the entire column.

 

https://support.microsoft.com/en-us/office/locate-and-reset-the-last-cell-on-a-worksheet-c9e468a8-0f...

@JMB17 

 

Good morning, and Thank you so much!

 

I was wondering, typically we have multiple sheets in our workbook but I only want the macro to run on one of the sheets.  Am I able to assign this macro to only run on one sheet rather than the many sheets we may have in a workbook?

From what I see, it should only be running on the range that is given to it.
Hi all
I don't know if this thread is still open but in the last week 2, when a long macro that I run to simulate football matches finishes, Excel becomes very slow; when I type, it takes about a minute or two to be able to do so. When I restart Excel, it all works fine. Similar to the original post.

Strange thing is, I ran the same macro from a backup file from 2 years ago and same problem occurs, whereas back then, it would complete fine. The only things that have changed for me are Windows 11.

I use the same techniques such as disabling conditional formatting and screen updating, so not sure what else I should be optimising something that wasn't broken before. Is there some sort of caching that Excel builds up when it runs macro and can only be cleared after a restart?
There are some other users that have experienced the same thing. It appears it could be due to a recent update/patch Microsoft pushed:

https://techcommunity.microsoft.com/t5/excel/macros-suddenly-run-much-slower/m-p/3136265

https://techcommunity.microsoft.com/t5/excel/excel-macro-runs-painfully-slow/m-p/3180940/emcs_t/S2h8...

One of the commenters, in the second link, believes it may be related to 'trusted locations' and was able to improve performance by adding the directory in which the workbook was located to trusted locations.

I and other users in my organization have recently experienced the same thing (file with auto open macro takes eternity to open), but moving the file to the machines local D drive fixed it (and it's not a network speed issue). So, whatever MS did, it appears to be related to the file's location.
I did suspect that, glad I'm not the only one.

Thanks v much for the links, I'll review at my next opportunity