Slow loading workbook

[Ken Warthen]

I've been asked to overhaul a workbook of some fifty worksheets with dozens
of macros and a messy user interface. I plan to convert the macros, which
appear to have been created using the macro recorder, to VBA code, implement
named and dynamic ranges wherever applicable and redesign the interface into
something more visually appealing as well as functional. The biggest problem
from a user standpoint is the workbook takes more than a minute to open.
That's pretty much unaccepatble, but I'm not really sure of what can be done
to significantly improve the load time. Any help, direction, or suggestions
will be greatly appreciated.

Ken

 

[Barb Reinhardt]

You could do one of the following
- Turn off calculation and see how fast it opens
- Check the WOrkbook_Open event to see what it does.

I'm sure someone else has another idea.

HTH,
Barb Reinhardt

 

[Sam Wilson]

I once inherited a workbook (in Excel 2003) where a user had put a zero in
cell IV65536 in a few of the sheets and that took an eternity to open. Delete
any unused rows & columns...

Then I'd look at your formulas - worksheets that we have that are typically
slow have large numbers of vlookups and sumifs. If you have a macro that hard
codes this area on demand you can make the whole think slicker.

Sam

 

[Ken Warthen]

I changed calculation options from automatic to manual, deleted all macros
and code modules, saved the workbook, closed it, and then reopened it. It
still took a minute-twenty seconds to open. That seems unreasonable even for
a workbook with fifty worksheets. Other complex workbooks on my computer do
not take any where near as long to open. Any other ideas on what might be
bogging this one down?

Ken

 

[J_Knowles]

Use Explore (right click start, select explore) to check the size of the
spreadsheet file. If the spreadsheet file size is 5MB or larger, it will
take 20-30 seconds to load.

If the file is large but you know the 50 worksheets only have limited data,
do the follow:

This will take several minutes, but may help you in the long run.
Do this command on all worksheet (50 times).

Start on the first worksheet, then press Ctrl+End this takes you to the last
cell used on the worksheet. If you find a cell way out of bounds, delete all
of the unnecessary columns & rows. You could have data misplaced in wayward
cells.

Press Ctrl+Home to back cell A1. And go to next worksheet.

When finished with all 50 worksheets, resave the spreadsheet and check the
file size again.

If the file is significantly smaller you have solve your problem.

If not, you could copy & paste all of the sheets into a new workbook and
check the size.

HTH,