excel file huge

[nikko50]

I have an excel file that is 50mb and I don't know why it is that huge. I did not create this file and was asked to look at it and figure out why it takes 10 minutes to open? There is 3 tabs and very little data. How can it be so huge??

 

[Norie]

There could be many reasons for the size of the file.

Are there a lot of formulae/formatting?

Is there any VBA code?

Have you tried copying the data into a new workbook?

 

[nikko50]

This is a very basic excel book that has no formulas. I did notice in one tab in print preview that it showed 1200 pages all of which were blank. How can I fix that?

 

[Norie]

How can I fix that?

I would suggest copying the data into a new workbook.

 

[sssb2000]

i've had the exact same problem before.....

possible solutions:


-make sure you go through every sheet.....the author has most likely colored all of the sheets white so that the user would not see un-used cells....that takes massive amount of memory.....undo that by making them "no color" instead of white

another thing that you can do once you try that, would be to copy your sheets into a fresh Excel file. make sure to do it in the exact same order, and be sure to take your macros with you.


i had a file that went from 12 MB to 200 KB within a matter of minutes after i did the above two.


Best of luck,


Kevin

 

[cstlaurent]

Just cut and Paste your selected data into a new file.

it Should diminush it by a lot..

 

[sssb2000]

Niko,
those blank sheets ARE your problem.
they have been colored White....if there is no data, delete those sheets, and if there is data, un-color them

 

[just_jon]

You can run this, courtesy of DJR, and see f it slims down that wb.
<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> ExcelDiet()
    
    <SPAN style="color:#00007F">Dim</SPAN> x           <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> LastRow     <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> LastCol     <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
    
    <SPAN style="color:#00007F">For</SPAN> x = 1 <SPAN style="color:#00007F">To</SPAN> Sheets.Count
        Sheets(x).Activate
        LastRow = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        LastCol = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        Range(Cells(1, LastCol + 1), Cells(65536, 256)).Delete
        Range(Cells(LastRow + 1, 1), Cells(65536, 256)).Delete
    <SPAN style="color:#00007F">Next</SPAN> x
    
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0
    
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
    
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

</FONT>

 

[sssb2000]

Just_jon,
that is an impressive piece of code you posted there!

it reduced my file size from almost 7 MB to 2!
it made everything faster! thank you!

could you by any chance explain what it does?

 

[just_jon]

The code lookp thru each worksheet, finding the boundaries of used ranges, and then deletes everything outside that.

It might sound stupid to delete already empty rows/columns, as they just come back, but the thing of it is that Excel sometimes remembers that you once formated a whole row or column and considers that as an active par of your book, to be saved.