Excel Vba Macro Running Very Slow

The select method is common to see in vba code however it is often added to the macro where it isnt needed.

Excel vba macro running very slow. 5000 rows is very close to what i actually need although it does change often. To get a formula type it in any cell then select the cell go toolsmacrorecord new macro and record a macro pushing f2 then enter. Processors run at about 30 40 when this macro is running so it is not over taxing the processors and very little memory usage while running. Windows 7 64 bit office 2014 8 gig of ram citrix virtual desktop macro still extremely slow.

Then i tried to run it in excel 2016 with a 2017 dell laptop i7 processor kabylake generation with of course a great expectation that the dell laptop would just definitely kill it run the macro in perhaps 5x faster than the old hp laptop. I cannot provide to workboot due to company policy. Go forth and optimize. It works but its slow and prone to runtime errors.

Select can trigger cell events such as animations and conditional formatting which slow the macro down so removing unnecessary selects can significantly speed up your macro. 9 quick tips to improve your vba macro performance. Sub nocopyandpaste instead of. Slow a good bit slower than a very similar macro.

If you use the macro recorder you may have noticed that its fond of using the select method to explicitly reference things. One macro runs in 1 second the other the one posted takes about 15 seconds. Here is the macro coding the team is using. Both macros are basically doing the same thing just slightly different formulas.

Excel vba performance coding best practices. By passes the clipboard sheet1rangea1a200copy. I normally run this file in excel 2010 since a year ago with a 2012 hp laptop i7 processor with no problem and no hassle at all. So now you have a very simple way to make your code run much more efficiently and you hopefully understand why you need to temporarily shut off these settings.

Avoid the use of copy and paste whenever possible.