Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Aug 2013
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel: Solving Bloat and Corruption

    My Excel 2013 has been bloating from less than 1 MB to nearly 8MB. I've been reading a lot of advice and have tried several solutions (which is difficult because its so slow).

    I do know that several of the worksheets have many blank rows that Excel thinks are "in use". The normal advice is to delete those rows, but that doesn't work for me; pressing Ctrl-End shows that they are still in use.

    I used another trick to separate the file into its individual components. (You do this by changing the extension from ".xlsm" to ".zip", then extract it as a zip file.) From this I learned two things:

    - One worksheet is much, much bigger than it ought to be. (Yes, its one of those with excess blank rows.) Two other sheets are also big compared to the others, and should probably be smaller.

    - There are two secret files called drawing4.xml and vmlDrawing4.vml which seems to hold information about the checkboxes in the workbooks. They seem to make up more than half, size-wise, of the entire workbook. I suspect they are keeping track of every checkbox I've ever inserted and deleted.

    Saving the workbook as a binary (.xlsb) file does not shrink the Drawing4 files, but I think it shrinks the worksheets.

    While waiting for help from you folks, I'm going to try deleting that troublesome sheet and all of the checkboxes, then see what's changed.

    EDIT: Somehow it turned out that instead about about 800, I had over 16,000 checkboxes! Maybe the macro I used to place them added multiple ones, aligned so that they weren't all visible.

    Anyway, after deleting them, my file actually got bigger: about 10 times bigger!

    I traced it to one of the sheets. The "Used Range" used to go up to Column P, but now it goes to Column WVU. So that's 16,000 extra columns in the worksheet that I still can't delete.
    Last edited by ShawnVW; 2013-10-01 at 19:18.

  2. #2
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    Pittsford,NY
    Posts
    874
    Thanks
    517
    Thanked 35 Times in 27 Posts
    Sorry, couldn't resist:
    When I saw the title of your posting I immediately thought of the Federal Government. Wouldn't it be nice if we could solve that problem.
    Dick

  3. #3
    Super Moderator jwitalka's Avatar
    Join Date
    Dec 2009
    Location
    Minnesota
    Posts
    6,794
    Thanks
    117
    Thanked 799 Times in 720 Posts
    Quote Originally Posted by Dick-Y View Post
    Sorry, couldn't resist:
    When I saw the title of your posting I immediately thought of the Federal Government. Wouldn't it be nice if we could solve that problem.
    Dick
    Please don't follow up on this. I have very strong feelings on this issue but this is a technical forum with no place for a political debate. I realize you didn't intend to start one Dick-y but it doesn't take much on this topic.

    Jerry

  4. #4
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    Pittsford,NY
    Posts
    874
    Thanks
    517
    Thanked 35 Times in 27 Posts
    Sorry Jerry, it won't happen again.
    Dick-y

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    After deleting the "empty" rows and columns have you saved, closed, and then reopened the file and tested it?

    If you have code that adds buttons, before adding do you have the code delete any old buttons (it sounds like your do not). Instead of saving as a ZIP, you may try saving as an HTML then reopening it. the HTML route sometimes can get rid of corruptions.

    If possible, You may want to copy the contents of each sheet (not copying the sheet, but the contents) to a new workbook, this sometimes can eliminate some corruption.

    Steve

  6. #6
    New Lounger
    Join Date
    Aug 2013
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    After deleting the "empty" rows and columns have you saved, closed, and then reopened the file and tested it?
    When I did, it seemed to work. I'm not exactly sure why the corruption moved from one location to another. I think it helped that I reset the formating in the blank cells before trying to delete them.

    Thanks.

Tags for this Thread

Posting Permissions

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