how to speed up a big file

[DutchKevin]

Dear All,
maybe a question that is hard to answer at once but i have an question about a xlsm file thay i manage, now around 15MB.
there are numerous sheets (>150) in it with all kind of links within the file, to other files, and also sevaral VBA actions.
Now a full recalcualtion of the file takes around 30secs on a good computer. i already work around this as much as possible with only recalculating individual sheets.

I would like to try to speed up the whole file for increased useabilty, but is there any way that this file can be analysed/monitored during a recalculation to show what is the biggest time consumer? so that i can start there with my attempts to speed things up.

Thanks,
Kevin

 

[Andrew Poulsom]

You could try to reduce the number of worksheets. This may give you some pointers:

Importance of Excel Calculation Speed - Decision Models

And you may want to purchase this:

FastExcel Customers and Comments - Decision Models

 

[JackDanIce]

Without seeing the file, difficult to suggest where. Go to each worksheet and press CTRL+END and see if the last cell is far away from what you think the last cell is and then delete the rows and columns not used inbetwee and save, that usually reduces some of the file size.

Check if you're looping, can you find alternative ways to achieve the same in your code, may be other parts of your code that aren't fully optimised or the formulas/links you're using in your workbook

 

[DutchKevin]

Thanks for your suggestions. I checked the end of some sheets, but the end is where it should be. and i can't reduce the nr of sheets as they are kept as archive.

 

[Andrew Poulsom]

Can't you move the archive sheets into another workbook?

 

[Michael M]

Do you have lots of:...
Charts
Pivot tables
Graphics
macros

on any of the sheets ??

 

[DutchKevin]

Hi,
The file doesn't have many charts, there are some 10-15 in total maybe on 2 or 3 sheets.
pivots are not used
the file is linked to 4 other files in total, where almost all sheets link to at least one common file.
all sheets have many conditional formats, some depending on external sheets info.

Regards
Kevin

 

[Michael M]

Something you could try is:

   Open the Excel file.
   Save the Excel file in .html format.
   Then save the .html document into .xls (or whichever format you have), but as another name.