Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    'Exploding' file size (Excel 2003)

    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

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Exploding' file size (Excel 2003)

    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.

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: 'Exploding' file size (Excel 2003)

    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),.

  4. #4
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Exploding' file size (Excel 2003)

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

    Thanks Rudi, that just saved me 20,070kb. My template filesize has dropped from 19.7mb to 166kb. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

    I've been pulling my hair out <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>, trying to streamline code and delete non essentials, and it was all in the [totally unnecessary] formatting!

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Exploding' file size (Excel 2003)

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


    Thanks,
    Brad

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 'Exploding' file size (Excel 2003)

    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.

  7. #7
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Exploding' file size (Excel 2003)

    Interesting...


    thanks for the info....


    Brad

  8. #8
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Exploding' file size (Excel 2003)

    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.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  9. #9
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Doorn, Netherlands
    Posts
    311
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Exploding' file size (All)

    Rudi,

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

    Regards, Teunis

  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    'Exploding' file size (All)

    * 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. <font color=red>***</font color=red>(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 <img src=/S/free.gif border=0 alt=free width=30 height=15> 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.)

    <font color=red>***</font color=red>
    Sub ReduceWBSize()
    'From: http://www.contextures.com/xlfaqApp.html#Unused
    ' 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 581,649</post#>
    <post#=589,833>post 589,833</post#>
    The thread staring at: <post#=581,443>post 581,443</post#>
    A web page to Decrease Excel Workbook Size

    Hope something here will help you!!
    Regards,
    Rudi

  11. #11
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Exploding' file size (All)

    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.
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

  12. #12
    New Lounger
    Join Date
    Mar 2006
    Location
    Wesseling, Nordrhein-Westfalen, Germany
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    another code to adjust UsedRange

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •