size of file

  • Thread starter Thread starter jb
  • Start date Start date
J

jb

does anyone know what makes the excel file size balloon
and what I can do to shrink it back down?
 
Excel only maintains information on cells it thinks are in use. This can
include cells that contain formatting, change in row height, etc. They
don't necessarily need to contain information. If you use the vertical
scoll bar and when you get to the end of you data, it isn't on the bottom
end of the bar, the Excel is seeing a lot more of your sheet as being in use
than you would think. This can cause excel to store much more additional
information and could be the cause of your problem. To reset the usedrange,
select entire rows from below the last filled row to the bottom and do
Edit=>Delete. An easy way to do this is to go in the name bar in the upper
left of the formula bar and enter

300:65536 <cr>
Then do edit=>Delete

Do it on each page with the appropriate ranges. Then save your workbook.
 
Thanks Tom. the odd thing is, I deleted thousands of rows,
and it only reduced the size by about 5kb! There's a temp
file that shows up as it saves, at about 1000kb, but the
actual file still saves at 2000kb...?
 
Charles Williams has a WebPage that offers a bunch of suggestions on
reducing file size.

http://www.decisionmodels.com/optspeedd.htm#Size

One issue that I found recently to cause significant bloating has to
do with running a lot of Web Queries, that is, getting External Data.
Excel creates hidden names.

I either delete these sheets periodically (through code). Or delete
these names........

Dim nm as Name

For Each nm In ActiveWorkbook.Names
If nm.Name Like "*ExternalData*" Then nm.Delete
Next nm

HTH
Paul
 
Do you have VBA in your workbook??? if so, delete it,
delete the modules that contained the code, create new
one and paste your code in it... do not ask me why but
you can reduce the size of your workbook up to 1/2 of
it's original size...
 
Back
Top