Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Sep 2001
    Location
    Birmingham, Alabama, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Memory Problem in Excel 200 (Excel 200)

    I have a very large workbook that I inherited. There are about 35 sheets in the workbook. The workbook is about 8.5 Meg. I recently needed to send one of the worksheets to someone. I deleted all but the one sheet from the workbook and saved it using a different name. When I went to add it to an e-mail I noticed that the file size was over 2.3 meg. I thought this was strange since this was not a big sheet.

    I opened the sheet and deleted all the rows and columns that did not have data in them. (I selected the Rows / Columns and did an edit

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

    Re: Memory Problem in Excel 200 (Excel 200)

    There might be formatting applied to the "empty" cells.

    You might try exporting the workbook to HTML, then open the HTML file in Excel and save it as a workbook again. Do check whether everything still functions correctly.

    Note for Excel 97 users: this method can't be used in Excel 97. I used to use the attached code there. It won't work with protected worksheets.

  3. #3
    Lounger
    Join Date
    Sep 2001
    Location
    Birmingham, Alabama, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Memory Problem in Excel 200 (Excel 200)

    Hans,

    I made it back to the office today and tried your suggestion. After exporting the workbook to HTML and then saving it as a worksheet again the original file was about 200K smaller. I also received warning messages about problems with Validation rules. Since I know I have one worksheet with a 2 meg problem I suspect the change was related to the Validation rules that were not carried forward. I also ran your macro to see if it would help. No luck. It reduced the size of the sheet only slightly

    Looks Like I will have to go in and build new sheets. Not a pleasant job with all the links between sheets.

    Just curious, have you ever seen a sheet with extra cells formatted where doing an edit clear all and then deleting rows and columns did not get rid of the formats?

    Thanks for the help. I hope you and the others on this site that answer the questions know how much you are appreciated.

    M

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

    Re: Memory Problem in Excel 200 (Excel 200)

    Thanks <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    I don't know how Excel works internally, but apparently worksheets accumulate crud over time. If the standard cleaning routines don't get rid of it, you may have to copy the data to a new sheet (don't copy an entire sheet, just select the used range), or even recreate it. I know it's a pain.

Posting Permissions

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