Rows hide - speed optimisation
I am using the following code to hide rows, that correspond with a certain criteria.
However, it takes Excel hours to finish the calculation and hide the desired rows.
Please Login or Register to view this content.
Please advise me how to optimise the script.
Why would you want to do that in the calculate event?Please Login or Register to view this content.
Entia non sunt multiplicanda sine necessitate
Well, it shouldn't necesarily be a calculative event. However, if I don't miss something, no matter what the event is, it will take the same time to complete.
The trigger is actually a range of dates that rerer to certain rows.
Thanks shg, it is much better now. Still, it takes about a minute to complete.
I actually need it once a month, but have no idea how to "shedule" a recalculation with such occurance...
It should take a lot less time now. When you hide a row in Excel 2003, it triggers a calculation, so it would execute the code repeatedly.
Try changing it to this. It will execute when the sheet is activated.
Please Login or Register to view this content.
This will restrict the action to only the relavant rows. And allow formulas that return logicals to control the row visiblity
Please Login or Register to view this content.
_
...How to Cross-post politely...
..Wrap code by selecting the code and clicking the # or read this. Thank you.
Mike's approach is, of course, valid. The two approaches, in general, will give different results, so look and see which, if either, does what you want.
I tried a number of different scenarios, the most efficient of which proved to be the one that triggers a calculation on sheet deactivation. This elliminated the "calculation lag" of the model.Originally Posted by shg
Try changing it to this. It will execute when the sheet is activated.
Please Login or Register to view this content.
The code is as follows:
Please Login or Register to view this content.
Shg and mikerickson, thank you both for your help
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks