HUGE Excel 2003 File Size

  • Thread starter Thread starter sharyn
  • Start date Start date
S

sharyn

I was working with Excel XP until last week. My file was
around 100K. Now under Excel 2003, the same file has
blossomed to over 3 MB! Is there any way to
compress/reduce the file size of this Excel file? I have
added about 20 rows of data but there are no macros in the
file and nothing (that I can see) that should have
resulted in size a growth in file size. Thank you to
anyone who can help.
 
i've been using xl2003 for a while now,
but haven't experienced anything like that at all.

however.. excel stores stuff until the infamous "lastcell"
which is not the last cell with data, but rather the last
cell with data or formatting.

maybe you accidentally formatted some cells?
or excel being excel.. it got confused of it's own accord...
(often if you hide rows until row65536, or columns until IV)


FOR EACH SHEET:

try going to the last cell with [ctrl]+[end]
if that isn't the cell you thought it would be..

then:
clear formats beyond your REAL lastcell
delete empty columns and rows beyond your "REAL" lastcell.

then:
open Visual Basic Editor:

in the immediate pane type
? activesheet.usedrange.address

(the usedrange method resets the lastcell property)

(Repeat for each sheet)



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
Hi Sharyn,
maybe you accidentally formatted some cells?
Click to expand...

In Excel 2000 that would not make any difference to the
used range. I think formatting is kept track of as a change for
calculation in Excel 2002, so does formatting cause a change to the
used range after Excel 2000.

The usual causes of the used range being too large are
- inserting rows /columns and then deleting rows/columns
- conversion from something else, like Lotus 1-2-3, conversions
are only interested that the data is correct, not that used range is correct.

When manually deleting the excess rows and columns, you also have to
save the file. Within a macro where saving isn't a big consideration there
are some tricks that make a save normally not necessary. (In Excel 95
you would have to save and exit with File, close when doing this manually).

Making the activecell the LastCell
http://www.mvps.org/dmcritchie/excel/lastcell.htm#makelastcell

David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
 
David..
In Excel 2000 that would not make any difference to the
used range.
Click to expand...

My point was that accidental formats DO make a difference to lastcell.
and as long as the usedrange method is not EXPLICITLY used (or the file
saved for xl97+ ) it's derivatives like lastcell remain unchanged and
may throw errors.

Accidental formats CAN cause trouble/corruption in combinations with
row insert/delete.

Sub AccidentalFormat()
[iv100].Interior.ColorIndex = 3
[iv100].Clear
MsgBox _
Cells.SpecialCells(xlCellTypeLastCell).Address & vbNewLine & _
ActiveSheet.UsedRange.Address & vbNewLine & _
Cells.SpecialCells(xlCellTypeLastCell).Address
End Sub


cheerz!


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >


David McRitchie said:
Hi Sharyn,
maybe you accidentally formatted some cells?
Click to expand...

In Excel 2000 that would not make any difference to the
used range. I think formatting is kept track of as a change for
calculation in Excel 2002, so does formatting cause a change to the
used range after Excel 2000.

The usual causes of the used range being too large are
- inserting rows /columns and then deleting rows/columns
- conversion from something else, like Lotus 1-2-3, conversions
are only interested that the data is correct, not that used range is
correct.

When manually deleting the excess rows and columns, you also have to
save the file. Within a macro where saving isn't a big consideration
there are some tricks that make a save normally not necessary. (In
Excel 95 you would have to save and exit with File, close when doing
this manually).

Making the activecell the LastCell
http://www.mvps.org/dmcritchie/excel/lastcell.htm#makelastcell

David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
Click to expand...
 
Back
Top