Macro file doc takes too long to open

[Mary7903]

Hello,

My spreadsheet sheet (macro file) has only 12 tabs and a few formulas but it takes ages to open and whenever I modify something it also takes away too long to save. As a result, most of the time Excel crashes - using excel 2010. It seems that the document is huge... is there anything I can do to solve the issue? Thank you

 

[steve the fish]

It could be a number of reasons but fairly often an unexpectedly large sized spreadsheet is caused by used range issues. If you go to a sheet and press CTRL-END the last cell is way past your actual data. Anyway you could try running this and seeing if the size reduces. It saves the book so makes sure you want that to happen.

For Each sh In ThisWorkbook.Worksheets
    sh.UsedRange
Next

ThisWorkbook.Save

 

[Mary7903]

It could be a number of reasons but fairly often an unexpectedly large sized spreadsheet is caused by used range issues. If you go to a sheet and press CTRL-END the last cell is way past your actual data. Anyway you could try running this and seeing if the size reduces. It saves the book so makes sure you want that to happen.

For Each sh In ThisWorkbook.Worksheets
    sh.UsedRange
Next

ThisWorkbook.Save

Hi Steve the fish,

Thank you for the above code. At the moment I have this code for each sheet: Sub Home() ThisWorkbook.Sheets("Home").Activate End Sub. Where do I add the code you provided?


Thank you