Compact or Shrink down excel file

[James C.]

Is there a way to compact an excel file (similar to compacting a database in
MS Access). I currently have an excel file that continues to get bigger even
though I have removed data from it. I have had this happen before and my
solution was to copy all of the contents to a new excel workbook and the size
was reduced dramatically.

This time however, I have too many sheets to do this.

Any thoughts?

 

[Harlan Grove]

Is there a way to compact an excel file (similar to compacting a database in
MS Access). I currently have an excel file that continues to get bigger even
though I have removed data from it. I have had this happen before and my
solution was to copy all of the contents to a new excel workbook and the size
was reduced dramatically.

This time however, I have too many sheets to do this.

Any thoughts?

 

[Harlan Grove]

solution was to copy all of the contents to a new excel workbook and
the size was reduced dramatically.

This time however, I have too many sheets to do this.

I'd guess you mean you have too many sheets to make it practical or
desirable to do it manually. So use a macro to do it. Record what
you'd normally do, then generalize it. If you need help generalizing
it, post a copy of the recorded macro in a follow-up.

 

[Gord Dibben]

James

What Excel thinks is the used range on a worksheet can increase over time with
cells being added to then cleared, not deleted.

To reset the used range on a sheet you can delete all unused columns and rows
then save the workbook.

To do many sheets at once would require a macro.

Debra Dalgleish has such code at her site.

http://www.contextures.on.ca/xlfaqApp.html#Unused

Note the caveat about "merged cells" and how to deal with them.


Gord Dibben MS Excel MVP