Excel turn off screen updating
), thus finding your cell on row 1000, and activating every cell between there and 55,000 when yor criteria is met, requires a lot of calculation from Excel. It only becomes an issue if your code errors out and you end the run (since updating is false, you can't click on the cells).I asked my coworker how long this normally takes her (because it is a horribly written macro that was hacked together) and she said about 15-20 seconds...hardly noticeable she says. Easy enough to fix though, just go to your Immediate window and turn Screen Updating to true.The Screen Updating property resets at the end of a procedure.
This spreadsheet that is open though has along the lines of 60,000 rows (good thing we are on 64 bit machines! There is no harm in turning Screen Updating off from my experience.Fortunately you can make Excel 2010 stop updating your formula totals whenever you make a change to a cell and instead execute all of your formula calculations manually.Note that this method will not stop Excel from executing formulas that consist of only numbers and mathematical operator symbols.The other option is to use a simple line of code like: Application.
Screen Updating=False This will stop Excel having to repaint the screen each time the macro does something.
You can click the Calculate Now button in the Calculation section of the ribbon when you are ready to update your formula values.