Managing very large workbook

[Ken Warthen]

I've been asked to add some enhancements to a workbook designed several years
ago by someone else. In addition to the enhancement I've beed asked to
design, I'd like to clean up code, converting macros to VBA routines,
functions, etc. The current workbook is about twenty-eight megabytes and
takes considerable time to load. There are about fifty worksheets in the
workbook. Any suggestions on how I might improve load time?

Ken

 

[PMC1]

I've been asked to add some enhancements to a workbook designed several years
ago by someone else.  In addition to the enhancement I've beed asked to
design, I'd like to clean up code, converting macros to VBA routines,
functions, etc.  The current workbook is about twenty-eight megabytes and
takes considerable time to load.  There are about fifty worksheets in the
workbook.  Any suggestions on how I might improve load time?

Ken

Hi Ken,

Unless the workbook is full of complex formulas I find generally its
unused or unnecessary formatting is the culprit. For each sheet select
everywhere on the sheet that doesn't contain data or formatting that
you want to keep then Edit > Clear > All (Or simple delete the rows
and columns). This has the effect of removing all unnecessary
formatting that can build up in older workbooks.

Note: if the workbooks are consistent you could select all worksheets
then delete all rows below a certain row and then do the same for the
coloumns

 

[Don Guillett]

Be sure to SAVE to see the changes
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

I've been asked to add some enhancements to a workbook designed several
years
ago by someone else. In addition to the enhancement I've beed asked to
design, I'd like to clean up code, converting macros to VBA routines,
functions, etc. The current workbook is about twenty-eight megabytes and
takes considerable time to load. There are about fifty worksheets in the
workbook. Any suggestions on how I might improve load time?

Ken

Hi Ken,

Unless the workbook is full of complex formulas I find generally its
unused or unnecessary formatting is the culprit. For each sheet select
everywhere on the sheet that doesn't contain data or formatting that
you want to keep then Edit > Clear > All (Or simple delete the rows
and columns). This has the effect of removing all unnecessary
formatting that can build up in older workbooks.

Note: if the workbooks are consistent you could select all worksheets
then delete all rows below a certain row and then do the same for the
coloumns