Huge File Size

[Guest]

I have a problem, I have a 18 sheets, all of which are linked via an ODBC to
SQL to pull in data, I thought that this was a good idea, now that it's done,
my file is 42 MG, any suggestions on how to shrink it while leaving the
links?

I have a work around, just dont' want to lose my mornings work.

Richard

 

[Otto Moehrbach]

Richard
There are a number of causes for a file being larger than what it should
be. The most common of these is where Excel thinks the last cell in the
used range is not what you think it should be.
Let me give you an example. Open a new blank file. Type something in
A1. Select say F10. Do Ctrl - End. Excel will move the cursor (active
cell) to A1. This means that Excel thinks A1 is the last cell of the used
range. And that agrees with you. Now select F10 (or any other cell) and
type something into that cell. Now do Ctrl - End. Excel jumps to that
cell. Again that is right. Now delete the entry in that cell. Now do
Ctrl - End. The active cell is what Excel thinks is the last cell of the
used range. You know it should be A1. The file is now bigger than it
should be.
Add hundreds of columns and thousands of rows to what you just did and
the file can get big in a hurry. Look at each sheet in your file and find
what you know to be the last cell of your data. Then do Ctrl - End and see
what Excel thinks is the last used cell. If you feel industrious, you might
figure out the number of cells involved here. Your data may take 1000 cells
while Excel thinks it's 50,000 cells.
If this appears to be your problem, you will need to delete all the rows
and columns after your data. Here's how you do that.
Let's say your actual last cell is J500.
Click on the row number 501. Click on the row number, not just some cell in
row 501.
Do Ctrl - Shift - down arrow.
This selects every row from row 501 to the last row in the sheet.
Put the mouse cursor in the selected range and right-click.
Click on Delete.
All the selected rows are deleted and replaced by blank rows.
Now click on the column letter K.
Do Ctrl - Shift - Right arrow.
Put the mouse cursor in the selected range and right-click.
Click on Delete.
Save the file.
If you have more sheets in your file than you want to do this with, post
back. There is code available to automate all this.
HTH Otto