Saving xlsb with VB HUGE FILE SIZE

[craig80]

Hello All,

I have created a little bit of code that opens, calculates and resaves a few reports.

These newly calculated wbs are then saved to a communial network drive. as .xlsb.

All easy up to now, the machine i was using to do this, was on 2003. I am using 2007 to create all macros. I thought this was the reason the 11mb files were saving as 140mb. So, i made a request for 2007. Duely installed by IS, great it though... ran my code. Boom... 146mb now.

Has anyone any experience in this?

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=" "C:\Documents and Settings\Craig\Desktop\Master Report " & Format(Now() - 1, "dd mmm yyyy") & ".xlsb", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
ActiveWorkbook.Close
Application.DisplayAlerts = True

Original files are all xlsb also, so i am not sure what is going on.

Any help would be greatly appreciated.

Thanks

Craig

 

[Gixerstu]

Go through each of your sheets and check to see what area is used.

I had a problem with this a while ago and it turned out that I had inadvertantly formatted some cells all the way to the bottom meaning that excel thought that the entire sheet was being used giving me massive file sizes.

The quick way to do it is to look at the scroll bar on the right, If the indicator is big then you should have no problem but if it is tiny then you have formatted a lot of cells you may not need.

Clear any formatting, conditional formatting and data validation in unneeded areas, save the file and you should be OK. Alternatively delete the rows.