This came up in another thread and to avoid confusion, I am separating this into this new thread....
I recently discovered "Custom Views" - it seems incredibly useful for "tuning" the contents of a complex worksheet to suit the needs of producing a particular report - eg. the View can hold some specifics such as:
- Values of any Filters;
- Hidden rows/colums;
- Print settings, including Sheet Header/Footer details.
However, I find that a worksheet/book that works fine (despite having a lot of complex calcs) suddenly behaves like a dog the moment I define and activate a Custom View. This is the scenario:
The environment is:
- The Excel workbook is fairly large (though only 2MB on disk), multiple Tabs, lots of data / formulae, and some links to another workbook.
- The Tab I'm working on and seeking to format for printing is roughly 100*1000 cells; and no filters are in place on this tab
- Working on a well-resourced Desktop computer, Win7.
For sake of demonstrating the issue simply --- I open the workbook and go to the tab I'm interested in, and define a default Custom View (all rows and columns of this sheet visible). I then hide one column (a text/comment column) and the define a Custom View for this state. I then "show" the 1st (default) Custom View, and observe the following:
1) It takes about a minute for the Show view to complete!!
2) Thereafter, moving around the Tab, or any part of the workbook, is painfully slow.
3) If I close the workbook and then re-open it, it opens in the last selected Custom View, and performance is normal.
4) If I now select the 2nd view - behaviour is bad as above - it takes a long time (1+ minute), and performance is again woeful until I close and re-open the workbook.
Has anybody had experience with these kinds of performance issues with Custom Views, or have any insights into what characteristics of the workbook can cause them??
Many thanks
Bookmarks