+ Reply to Thread
Results 1 to 10 of 10

Adding and deleting empty sheets increases file size.

  1. #1
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,694

    Adding and deleting empty sheets increases file size.

    Adding and deleting empty sheets increases file size.
    I saw some code on one of these sites, and unfortunately I don't know where and who's code it is so I cant thank him/her, and I wanted to compare it to some other methods of deleting sheets.
    Following is the Function and macro I am talking about.
    The timers were added by me to give me an idea for comparing purposes.
    When I rune these macros several times, the file size goes from an original of 24 KB (See attached) to 600+ KB
    Nothing is added to the 3 sheets but I checked that just in case.
    What is the culprit that increases the file size?
    All these codes are in the attached workbook
    Please Login or Register  to view this content.
    I use the following code to add sheets.
    Please Login or Register  to view this content.
    The following three macros also are used to delete back to 3 sheets.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,937

    Re: Adding and deleting empty sheets increases file size.

    Hi, I took a look and it happens. What also happened if you check the file that after you have added the worksheets and you delete them, a new sheet after that will be numbered 1003
    I added a small function that calculates the filesize before and after the macro run.
    When you save the workbook it levels down a little but still increase.
    I don't know where that comes from but something tells me that Excel 'remembers' the worksheet count what could cause it.
    If you save the file close Excel and reopen then the worksheet count is back to 'normal'
    I've re attached the workbook with the original name including the FileSize function
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,694

    Re: Adding and deleting empty sheets increases file size.

    Hi Keebellah (is dat Hallebeek?)
    Thanks for looking at it. I am still dumbfounded. As we all do, after using it we save it in a folder and the file size does not change. It remains a large file.
    I guess what I'll end up doing is not using one macro and use the other ones several times and see what that does. After that, go on to the next.
    Thanks again for your help.

  4. #4
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,937

    Re: Adding and deleting empty sheets increases file size.

    Well, Excel does 'move in mysterious ways'
    Yes, you got the name right

  5. #5
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,694

    Re: Adding and deleting empty sheets increases file size.

    Hi Keebellah,
    Just finished running each code ten times.
    The "Add_A_Bunch_Of_Sheets" followed by "Delete_Added_Sheets_With_Loop" had no file size increase.
    However, adding and deleting with the other three each increased the file size by +/- 350 KB. I guess if you don't do it too often it won't be a problem!!!
    Still don't know why though. Maybe we will keep it at your possible reason (Mysterious). Excel is the culprit.
    Thanks again and have a good day.

  6. #6
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,937

    Re: Adding and deleting empty sheets increases file size.

    What Office version are you using?

  7. #7
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,937

    Re: Adding and deleting empty sheets increases file size.

    I tried two things but the minimum filesize at all times goes to 330,174 as xlsm and 323,550 as xlsb
    I attached the files as zip file because the xlsb is not accepted
    Attached Files Attached Files

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    90,774

    Re: Adding and deleting empty sheets increases file size.

    They should be allowed!
    Attached Images Attached Images
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  9. #9
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,937

    Re: Adding and deleting empty sheets increases file size.

    Okay, I remember the last time I tried it (loooooong time ago) it was not allowed, and honestly, I did not try again, I just zipped, will keep it in mind.
    Thanks for the update and pointing it out, I'm a 'lazy' reader

  10. #10
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,694

    Re: Adding and deleting empty sheets increases file size.

    Re: What Office version are you using?
    Office 2007.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. excel file size increases on its own
    By umbata in forum Excel General
    Replies: 2
    Last Post: 04-02-2014, 07:05 AM
  2. Replies: 5
    Last Post: 09-13-2012, 11:50 AM
  3. File size increases significantly with Macros
    By tdyl1969 in forum Excel General
    Replies: 1
    Last Post: 04-27-2011, 08:02 PM
  4. [SOLVED] Excel file size increases when editing same info
    By Ron Bevan in forum Excel General
    Replies: 3
    Last Post: 07-25-2006, 11:00 AM
  5. File size increases with each save
    By Garbunkel in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-05-2005, 06:05 PM
  6. [SOLVED] Why autoformat increases file size?
    By kdw in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-07-2005, 07:06 PM
  7. [SOLVED] Importing .txt data files increases .xls file size
    By BrianJ in forum Excel General
    Replies: 1
    Last Post: 01-29-2005, 10:06 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1