Condensing Speadsheets Size and Performance? 2

[jaw323]

I am an intern at my company and I was asked to research about a particular Excel spreadsheet that we use. It is a tool that we use that is in the form of a spreadsheet that is over 40 meg in size. It runs thousands of calculations, and lasts for 10 minutes when recalculating. My question is: Is there a way to improve the performance of a spreadsheet and condense the overall size? I am not sure where I could look to find an answer to this. Would it be benifitial to try and run many marcos in this spreadsheet and would this reduce the calculation times or file sizes?

Thanks

 

[SkipVought]

jaw,

If you are running macros, chances are that each macro uses alot of Activate and Select methods. Turning off ScreenUpdating will help, but redoing the macros to eliminate most if not EVERY Activate and Select will porbably do the most to speed things up. Check this out faq707-4105 How Can I Make My Code Run Faster?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[macropod]

Some of the things you can do to minimise file size and improve performance include:

1) Deleting unused rows & columns, especially when they're outside the used range. For example, if you press Ctrl-End on a worksheet and the cursor junps to an unused row or column, select and delete all of the unused rows/columns from there to the row/column just after the last used row, then re-save the workbook. You'll have to process unused rows & columns as two actions.

2) Avoid IF functions wherever possible. This can often be done where the IF statement always returns a numeric value. For example, =(A1="Data")*13 is equivalent to =IF(A1="Data",13,0) but is more efficient. Similarly, =(A1="Data")*(A2=5)*13 is equivalent to =IF(A1="Data"),IF(A2-5,13,0),0) or =IF(AND(A1="Data",A2=5),13,0)but is more efficient.

3) Don't use array formulae where non-array formulae will do the same job. Array formulae use lots of memory and are very resource-intensive. Macros and/or user-defined functions may also be more efficient where there is no non-array equivalent.

4) Don't build worksheets with lots of empty space between blocks of data/formulae. All that empty space chews up disk space an memory. Using more, compact worksheets may be more efficient.

I'm sure there are lots of others.

Cheers