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

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.

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.

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

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

Brilliant - thanks again Jason much appreciated