Excel file size: 12 273kb sheets = 14Mb? What's going on?

[rodwhiteley]

Hey,
I have a workbook with 12 monthly sheets, one data validation sheet, and one small name list sheet. The biggest (monthly) sheets are the monthly ones which are 34 columns by 260 rows, and saving these sheets separately results in about 270kb for each of these sheets. When I save the entire workbook however it's blowing out to >14Mb - it would seem that something extra is being saved, and I can't track it down. We are using these same workbooks at 14 sites, and the problem is only happening with one of the workbooks.
Any tips on how I might track down where the memory is going?
Note that all the other workbooks are about 5Mb, and working fine. The end user has no idea what happened of course.
Thanks in advance,
Rod

 

[rodwhiteley]

<Bump>

 

[rodwhiteley]

<Bump>

 

[FDibbins]

You really should wait a while before bumping your thread

Try this...
on each sheet, press END/HOME and see where you end up. If it is "out in the middle of nowhere", start deleting rows and columns until you get to where you KNOW the bottom-right-most cell is. Repeat this for teh other sheets.

Save-AS a test file, so you have a back-up (of the original file) in case you messed up. Close and re-open the test file and see if that fixed the issue

 

[Michael M]

It's possible that when you created / formatted / edited your workbook....it kept on accumulating the data
Have a look on the sheets and use CTRL + END to go to the last used cell and if it's waaaay down the sheet, that the likely cause.
If that is the case try this code, once run save and close the workbook....it should be reduced significantly.

Public Sub setALLUR()
Dim ws As Worksheet
For Each ws In Worksheets
    ws.Activate
    ActiveSheet.UsedRange
Next ws
End Sub

 

[rodwhiteley]

Thanks for the reply - just checked, and each of the larger sheets were only active to Cell AM260, ie 39 columns by 260 rows. I ran the macro, and didn't get a change in file size as I guess you'd expect, therefore.

Inadvertently I just solved the file size issue by File ... convert ... Save As .xlsx, so my suspicion is that something had happened here with the end user saving as a .xls and once I converted back to .xlsx all the memory was restored.
The sheets do use a lot of conditional formatting, and if I try to save as .xls I get warnings about loss of compatibility. I've not seen this issue reported before, and am still not sure this is the problem though.

Thanks again for the help,
rod

 

[FDibbins]

It may not be the CF itself, but may be a formula that is being used IN CF, that is not in versions earlier than 2007 - like sumifS()