So, I had a bit of VBA that I wrote to clean up and group a transaction level data set from a 3rd party report. I noticed that on average it was taking up to FOUR seconds per row to clean, identify, re-organize, validate for duplicate entries, and write to a new table. Essentially, 600 rows would take at least 30 minutes for the vba code to complete its task. I have NEVER run into this issue before - Turning off Screen Updating has resolved any issues with speed I've had up until this point.
30 minutes just won't do.
So, I went hunting for solutions. But, before I can solve the problem, I had to FIND the problem.
What is the problem? Well, I found through the use of breakpoints where the lag in time was. It was taking up to 3 seconds to simply place a batch of values that were stored in variables into empty cells.
example line:
Sheets("Converted").Cells(Rowc, 1).Value = Location
The issue was that Excel was recalculating the sheet every time a new value was placed in a cell. So, how do we stop that?
At the beginning of your vba code (probably right along with your line to deactivate screen updating) insert the following line:
Application.Calculation = xlCalculationManual
Then, at the bottom right before you end sub, insert the line:
Application.Calculation = xlCalculationAutomatic
BINGO! Problem solved.
No comments:
Post a Comment