B
Bob Flanagan
Just helped out a friend who had aworkbook which was opening very slowly.
On most of his sheets, the used range was well beyond the cells with
entries. For example, his last entry one worksheet was M300, but Excel as
saying it was IV2017. We tried deleting all unused columns and rows, but it
didn't shrink the used range, which surprised us. Deleting all shapes and
comments didn't do any good (we didn't keep that file). We then realized
there were hidden rows and columns so we unhide them and then deleted the
columns and rows beyond the real used range. We saved and re-opened the
file. The used range was now correct, and the opening time went from 20-30
seconds to 4-5 seconds. The key was the unhiding the rows and columns to
allow one to shrink the used range.
Hope the above helps others with similar problem. I'm not certain if the
hidden rows and columns caused the slow opening or the large used ranges.
Bottom line, when both were eliminated, things got a lot faster on opening.
If you have a slow opening problem on a file, try the above and let me know
the results.
Robert Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel
On most of his sheets, the used range was well beyond the cells with
entries. For example, his last entry one worksheet was M300, but Excel as
saying it was IV2017. We tried deleting all unused columns and rows, but it
didn't shrink the used range, which surprised us. Deleting all shapes and
comments didn't do any good (we didn't keep that file). We then realized
there were hidden rows and columns so we unhide them and then deleted the
columns and rows beyond the real used range. We saved and re-opened the
file. The used range was now correct, and the opening time went from 20-30
seconds to 4-5 seconds. The key was the unhiding the rows and columns to
allow one to shrink the used range.
Hope the above helps others with similar problem. I'm not certain if the
hidden rows and columns caused the slow opening or the large used ranges.
Bottom line, when both were eliminated, things got a lot faster on opening.
If you have a slow opening problem on a file, try the above and let me know
the results.
Robert Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel