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

rodwhiteley

New Member
Joined
Jan 15, 2012
Messages
37
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:confused:
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
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

Well-known Member
Joined
Oct 27, 2005
Messages
20,205
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

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.
Code:
Public Sub setALLUR()
Dim ws As Worksheet
For Each ws In Worksheets
    ws.Activate
    ActiveSheet.UsedRange
Next ws
End Sub
 

rodwhiteley

New Member
Joined
Jan 15, 2012
Messages
37
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

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
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()
 

Watch MrExcel Video

Forum statistics

Threads
1,130,336
Messages
5,641,565
Members
417,222
Latest member
Broflovski

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top