+ Reply to Thread
Results 1 to 6 of 6

Help in shrinking Workbook Size

  1. #1
    Registered User
    Join Date
    06-07-2014
    Posts
    3

    Help in shrinking Workbook Size

    Hi,
    Hope I'm posting this in the correct section - apologies if its not.

    I've been using a workbook I created for annual leave recording at my employer for a year or so. It fulfils my needs but needs to go on a shared network drive in order to allow my deputy to access the workbook.

    The problem is the sheet is 125mb in size and takes an age to download & then save to the companies network drive.

    The sheet is made up of the following:

    staff list - where hours are inputted, which then once sorted automatically feed in to

    weekly breakdown
    individual holiday record
    annual list.

    There are some macros & some conditional formatting in the sheets so that i can see when i reach the list of staff allowed off for the day.

    I did try uploading the file here but it was taking ages so here is a link where you can download it from.

    https://drive.google.com/file/d/0B7V...ew?usp=sharing

    Would some via coding make it more compact/slicker?

    Thanks in anticipation for your help

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Help in shrinking Workbook Size

    Your workbook is bloated with 'dirty' columns, i.e. columns that are empty but for some reason excel thinks they are in use.

    I expect that accounts for over 100MB of your file size.

    I'm going to try and delete them for you, but first attempt caused excel to crash. It might be easier to copy the useful data to a new workbook.

  3. #3
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Help in shrinking Workbook Size

    Got it down to under 1.5MB, had to zip it cos site limit is 1MB for attachments.

    I trimmed down one of your macros as well while I was looking for cause of the bloating. Hopefully I haven't trimmed it too much.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    06-07-2014
    Posts
    3

    Re: Help in shrinking Workbook Size

    Thanks for taking the time to hep Jason.

    I will check it out when I get into work this morning as I only have a Mac at home.

    Was getting rid of this "bloating data" something that is easy to do? I have 2 other similar spreadsheets that I need to adjust, as I have 3 departments that I run the spreadsheets for, so I can replicate what you have done on the other 2 sheets.

    Again many thanks for your help

  5. #5
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Help in shrinking Workbook Size

    I forgot to mention that I had to unshare the workbook to access the code editor when I was looking for the cause, so you will need to re-share it.

    To find problem sheets, look at the scrollbars in the bottom right corner of excel, and drag the bars to the right, and bottom. (don't use the arrows, or scrollwheel on your mouse, only dragging the bar works).

    If, when you do this, you end up way beyond the end of your real data (a few rows or columns is normal, but not hundreds, or even thousands), then you have bloating.

    ** If you have a lot of formula, then set calculation to manual while doing this, similarly if you have Event procedures in vba, then temporarily disable events. ***

    An unshared copy, saved on your computer, rather than a network location might also be a little more stable.

    If you have a consistent pattern in your sheets then maybe a simple code routine could be used for many sheets, for example, you had column bloat on all of the weekly sheets, so I just used code to delete columns J:XFD on any sheet where the name started with "wc ".

    To clear bloated rows, select a cell in the first empty row below your real data (on sheets such as your 'Annual Leave Cards', the empty yellow rows with the border of the card should be included in real data, but not the white rows below. Same applies to columns later).

    Next press Shift Ctrl and Down Arrow together, this will select the whole 'empty' part of the column.

    Right click in the selected area, then 'Delete' and 'Entire Row'.

    Similarly, for columns, select an empty cell in the first column to the right of the real data, then Shift Ctrl and Right Arrow, Right click, Delete, 'Entire Column'.

    Save after each sheet, it saves time if you get exceptional bloating and excel crashes. Note that each time you save the file size should reduce, but the 'bloating' will still appear to be there when you look at the scrollbars, this will 'correct' itself when you close and reopen the workbook.

    Uncheck the box on the 'View' tab to hide gridlines rather than colouring the cells white to hide them (using white cells with gridlines on increases bloat, 'no fill' with gridlines off does not).

  6. #6
    Registered User
    Join Date
    06-07-2014
    Posts
    3

    Thumbs up Re: Help in shrinking Workbook Size

    Brilliant - thanks again Jason much appreciated

+ 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. Replies: 4
    Last Post: 12-03-2013, 09:12 AM
  2. Shrinking text size-command button
    By chemi9 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-24-2012, 07:21 PM
  3. Shrinking Buttons
    By JimBobBowie in forum Excel General
    Replies: 8
    Last Post: 07-12-2011, 08:12 AM
  4. Shrinking Size of Excel File
    By Timothy100 in forum Excel General
    Replies: 5
    Last Post: 02-05-2011, 06:52 PM
  5. shrinking pop up calendar
    By hugrl in forum Excel General
    Replies: 0
    Last Post: 01-19-2011, 11:04 PM
  6. Shrinking a design???
    By smarch001 in forum Excel General
    Replies: 2
    Last Post: 03-26-2009, 12:46 PM
  7. Shrinking workbook size
    By emp1953 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-08-2008, 09:39 AM
  8. [SOLVED] My workbook links are not updating (its 30,000 KB size workbook).
    By rselena in forum Excel General
    Replies: 1
    Last Post: 08-14-2006, 04:20 PM

Tags for this Thread

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