80 MB File??

3LeggedDog

Board Regular
Joined
Feb 11, 2006
Messages
149
I have an Excel file which somehow ballooned to about 80 MB. The previous time I had checked before that, it was about 17. The file has data in about 19000 rows and maybe 60 or 70 columns. I can't even do so much as change a formula without it freezing up. I realize that it's a large file, but should it really be 80 MB, or is something weird going on?

Thanks
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
If you set calculation to manual before opening, you can try this code to get rid of unused ranges:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> WorkbookReducer()
    <SPAN style="color:#007F00">'From: http://www.contextures.com/xlfaqApp.html#Unused</SPAN>
    <SPAN style="color:#007F00">' Re-set used range</SPAN>
    
    <SPAN style="color:#00007F">Dim</SPAN> myLastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, myLastCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> wks <SPAN style="color:#00007F">As</SPAN> Worksheet
    <SPAN style="color:#00007F">Dim</SPAN> dummyRng <SPAN style="color:#00007F">As</SPAN> Range
        
        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> wks <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets
            <SPAN style="color:#00007F">With</SPAN> wks
                .Activate
                <SPAN style="color:#007F00">'.Unprotect "bigdog"</SPAN>
                myLastRow = 0
                myLastCol = 0
                <SPAN style="color:#00007F">Set</SPAN> dummyRng = .UsedRange
                <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
                myLastRow = _
                    .Cells.Find("*", after:=.Cells(1), _
                    LookIn:=xlFormulas, lookat:=xlWhole, _
                    searchdirection:=xlPrevious, _
                    searchorder:=xlByRows).Row
                    myLastCol = _
                    .Cells.Find("*", after:=.Cells(1), _
                    LookIn:=xlFormulas, lookat:=xlWhole, _
                    searchdirection:=xlPrevious, _
                    searchorder:=xlByColumns).Column
                <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0
                
                <SPAN style="color:#00007F">If</SPAN> myLastRow * myLastCol = 0 <SPAN style="color:#00007F">Then</SPAN>
                        .Columns.Delete
                    <SPAN style="color:#00007F">Else</SPAN>
                        .Range(.Cells(myLastRow + 1, 1), _
                        .Cells(.Rows.Count, 1)).EntireRow.Delete
                        .Range(.Cells(1, myLastCol + 1), _
                        .Cells(1, .Columns.Count)).EntireColumn.Delete
                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
            <SPAN style="color:#007F00">'.Protect "bigdog"</SPAN>
        <SPAN style="color:#00007F">Next</SPAN> wks

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

If that doesn't work, your wb may very well be corrupt.

HTH,

Smitty
 

Forum statistics

Threads
1,136,263
Messages
5,674,710
Members
419,521
Latest member
Jasonnie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top