‘Exploding’ file size (Excel 2003)

Topic

New Reply

Hi folks….I have a quick (I hope) question…I took a 9MB file (a scheduling file for 2008) and wanted to prepare the same file for 2009….I added 1 or 2 very small macros; I deleted a sheet in the workbook….when I saved the file, it had exploded to 19.5 MB……I didn’t add any new formulae or any new conditional formatting……the one thing I did was to open it and save it immediately as ***(v.2009).xls and at that time, there were a lot of sheets in ‘frozen pane’ mode…..could that have effectively doubled the file size??? And, if not that, any ideas as to what else might have caused it?? Thanks

Reply | Quote

Replies

Just a stab in the dark on this one.

Do you have and “objects” in the workbook? Items such as auto shapes or boxes or lines. The reason I ask is because I had a user who’s workbook had blown up to 9 MB and was running extremely slowly. He had 5 lines drawn on the works sheet. When I removed them, the file size was less than 100kb. He had been using the workbook as a template and re-saving over and over.

Reply | Quote

* Workbooks can grow larger than usual due to a number of reasons. The most common are reasons you should check for are:

– Having extra blank sheets in a worbook that are not being used. Delete these sheets as they contribute to the file size, even if blank.
– Having excessive formating in the file. Use the Clear formatting command to easily get rid of unnecessary formatting.
– Having object like shapes, pictures, clipart, diagrams and charts in the file. If they are not really needed, remove them.
– Using cells in the sheets, and then not resetting the last used cell. ***(See the macro below will help to reset unused cells)
– Using large nested functions. A common example are large nested if functions. If possible, replace them with a function that removes the nests, like VLookup in this case.
– Using array functions where not necessay
– A corrupt workbook could cause this problem. Copy the data into a new file.
– Using unnecessary code. Any VBA code is not the most optimal inclusion in a workbook. Before using code, find out if there is not an existing Excel feature that can do the job.
– Ensure there are no hidden sheets in the workbook. Check in the VB editor for hidden and very hidden worksheets.
– Check to see if the file is a Shared workbook (Tools > Share Workbook). It can retain all changes, that could potentially be part of the problem.
– Check to see if you do not have “ghost links” by checking the Names dialog for name ranges with #Ref in them.

* A saving technique that one can use to make a workbook smaller is to save it as html or xml, then to resave as xls again. See this webpage that explains the concept:
See: Reduce Size of Excel Files

* Try to select the range in the sheet and copy (just the range, NOT the entire sheet) to a blank new workbook. The current workbook could be corrupt, and simply copying the data (and ONLY the data) into a new blank workbook can solve the problem.

* You could also try this Excel File Size Reduce Software 7.0 to make your file smaller.
I have not tested it myself!!

I found this macro. It reduces the file size by looping through sheets and resetting the last used cell. Paste it into a module and run it and see if it does not help. (PS: Ensure that none of the sheets are protected, else the code will fail.)

***
Sub ReduceWBSize()
‘From: http://www.contextures.com/xlfaqApp.html#Unused%5B/url%5D
‘ Re-set used range

Dim myLastRow As Long
Dim myLastCol As Long
Dim wks As Worksheet
Dim dummyRng As Range
Dim AnyMerged As Variant

For Each wks In ActiveWorkbook.Worksheets
With wks
AnyMerged = wks.UsedRange.MergeCells

If AnyMerged = False Then
myLastRow = 0
myLastCol = 0
Set dummyRng = .UsedRange
On Error Resume Next
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
On Error GoTo 0

If myLastRow * myLastCol = 0 Then
.Columns.Delete
Else
.Range(.Cells(myLastRow + 1, 1), _
.Cells(.Rows.Count, 1)).EntireRow.Delete
.Range(.Cells(1, myLastCol + 1), _
.Cells(1, .Columns.Count)).EntireColumn.Delete
End If
End If
End With
Next wks

End Sub

* More web references to check:
Start Post – post 581,649
post 589,833
The thread staring at: post 581,443
A web page to Decrease Excel Workbook Size

Hope something here will help you!!

Reply | Quote

Thanks, Rudi…..I was able to go thru all the various steps…the ‘save as’ xml and then re-save as .xls worked nicely…..you saved me a lot of work with that tip…thanks again(and thanks also to mmbarron for his suggestion, as well),.

Reply | Quote

[indent]

---

Having excessive formating in the file. Use the Clear formatting command to easily get rid of unnecessary formatting.

---

[/indent]

Thanks Rudi, that just saved me 20,070kb. My template filesize has dropped from 19.7mb to 166kb.

I’ve been pulling my hair out , trying to streamline code and delete non essentials, and it was all in the [totally unnecessary] formatting!

Reply | Quote

Question,
Can someone please explain the “last Used Cell” issue? What does this do? Why reset?

Thanks,
Brad

Reply | Quote

If you activate a worksheet and press Ctrl+End, Excel will jump to what it considers to be the last used cell of the worksheet, i.e. the intersection of the last row that contains anything and the last column that contains anything.
If you’ve done a lot of editing in a worksheet, the last used cell may be further down and/or to the right than you’d expect. Sometimes Excel considers a row or column to be ‘used’ even though it doesn’t contain anything if you inspect it visually. This could be caused by stray formatting, or by a cell just containing a space, or something like that. If the used area of a worksheet is larger than expected/necessary, chances are that the file size of the workbook is also larger than expected/necessary. So it may make sense to delete or clear the superfluous rows/columns.

Reply | Quote

Interesting…

thanks for the info….

Brad

Reply | Quote

Hello together,

you can adjust the UsedRange for the active sheet by this code:

Sub adapt_UsedRange_in_active_sheet()
ActiveSheet.UsedRange ‘readjustment of UsedRange
End Sub

For all sheets in a workbook you can use this code:

Sub adapt_UsedRange_in_all_sheets_of_a_workbook()
‘only working in a loop, because sheet must be active
‘by Uwe Küstner – Germany
Dim Ws As Worksheet
Dim oWs As Object
Set oWs = ActiveSheet
Application.ScreenUpdating = False
For Each Ws In Worksheets
Ws.Activate
ActiveSheet.UsedRange ‘readjustment of UsedRange
Next Ws
Application.ScreenUpdating = True
oWs.Activate
End Sub

Reply | Quote

I found, once in the dim distant past, that a workbook with a large block of formatted cells took up a great deal more storage space than formatting just a group of columns or rows. That would make sense if Excel had to store information for each cell in a block, rather than just one piece of info for a line or column.

That was a few years ago, I have not experimented with that for many years, and certainly not in the most recent version (or two) of Excel.

Reply | Quote

Rudi,

In addition you could use the freeware ASAP Excel Utility (http://asap-utilities.com/%5B/url%5D) and/or not free Fast Excel (http://decisionmodels.com/%5B/url%5D).

Regards, Teunis

Reply | Quote

Thank you, Rudi!

I’m sure to be returning to your excellent post every now and then.

Yet another possible (and very valuable) solution can be found in this starpost by Jan.

Reply | Quote