Worksheet is exploding size of file

[rdw72777]

All,

We have a pricing file that extends about 500 rows and each three month period uses up all 255 rows. As such, every three months, we copy the prvious 3 months worksheet into a new sheet in the same file and use it for the next 3 months.

Recently, the newest 3 month file, once we began editing in it, has exploded the size of the file. It appears to add like 15 megs to the overall file size and slow everything down. However, if I delte this new sheet and copy the most recent 3 month sheet over, it only adds like 3 megs and the file is manageable.

Does anyone know a quick way to determine what has happened? The worksheet that is exploding the file size has a similar amount of formulas as the old one and the formulas are simple addition or division, according to the code below.

Sub CountFormulas()
MsgBox ActiveCell.SpecialCells(xlCellTypeFormulas, 23).Count
End Sub

Is this sheet corrupt and do I need to start over? I am trying to find what is wrong with this new sheet that is causing these issues, but the file now takes like 20 minutes to save and 15 minutes to insert a column, even with calculations turned off.

Any suggestions as to where to start? Anyone?

 

[just_jon]

You likely have phantom cells, with format ghosts and such.

Stick this in a module and run it, should pare down your size.

<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>

 

[rdw72777]

Just_Jon and everyone,

I am currently running your code (it has frozen my excel for a bit here). But, let me further the argument by saying that if I delete the contents of every cell in this sheet using the Edit-->Delete dropdown, the sheet is still hogging up more space than it should. If I delete this "empty" sheet, the workbook size goes from 45 megs to 32 megs; that's right deleting a completely empty sheet got rid of 13 megs.

What next...?

Ryan

 

[DominicB]

Good mornin rdw72777

Just Jon is probably right in what he is saying, but you can do something similar to what his macro does manually if you prefer. Go to the last row + 1 and highlight (over the numbers so you select the whole row) all the way down to 65536 and right click and select delete. Do the same to any empty columns you have and save the file. Close and reopen to see if it has been effective.

HTH

DominicB