Excel (very large file size - how to reduce ?)

[Jock1970]

I have just made up a spreadsheet to record holidays etc for 2005. I posted a question last week regarding conditional formatting on 7 different parameters and got great responses which did exactly what I wanted.
The spreadsheet is now exactly how I wanted it to be, but despite containing only 1 worksheet, is now 12.5mb in size.
As a result it takes a while to open off the server it's stored on. Is there a way to reduce the size of this file ? Have I done something wrong that is making the file so large ?
I expected the file size to be a few hundred kb at the most.

Here is the statement from the VBE window in case theres something in there making the file so large.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
For Each oCell In Target
Select Case oCell.Value
Case Is = "P"
oCell.Interior.ColorIndex = 5
oCell.Font.ColorIndex = 2
Case Is = "H"
oCell.Interior.ColorIndex = 3
oCell.Font.ColorIndex = 2
Case Is = "F"
oCell.Interior.ColorIndex = 50
oCell.Font.ColorIndex = 2
Case Is = "S"
oCell.Interior.ColorIndex = 6
Case Is = "T"
oCell.Interior.ColorIndex = 37
Case Is = "C"
oCell.Interior.ColorIndex = 26
Case Is = "O"
oCell.Interior.ColorIndex = 38
Case Else
oCell.Interior.ColorIndex = xlNone
End Select
Next oCell


End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub


Thanks for reading, hope you can help,

Cheers,

Jock

 

[koresnordic]

Any pivot tables? You can save the table without the underlying data. This will save a lot of space.



Graham

 

[Jock1970]

Hi Graham,
no pivot tables (at least, I don't think so - I've never used them and assumed they were something that you had to set up and don't create by accident).
If theres a risk that one has appeared 'by magic', how do I check ?

Cheers,

Jock

 

[koresnordic]

No they don't appear unless they are created. So if you don't know about them, you prob don't have any. How about trimming any excess rows / columns? Plus I know that Excel keeps all the changes inside the spreadsheet somewhere, but am not sure of how to delete this part. Maybe someone else can suggest how to do this?



Graham

 

[Jock1970]

Thanks for you help anyway, Graham

I appear to have solved the problem, but only by copying all the cells into a brand new spreadsheet then formatting the rows/column widths etc to mimic the original.
The file is now only 49kb !
I have kept a copy of the original 12.5mb version. I would still be interested to find out where I went wrong, or what the difference is between the original and my new version for future reference. Anyone ?

Cheers,

Jock

 

[xlbo]

Have you set formatting or have formulae referring to empty cells in case more data is brought in ??
Is it saved in the default excel version ?? if you save in earlier versions, it can cause bloating
Is the workbook shared ?? this can also cause file bloat

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[klklkl]

One way that the file gets bloated is if you set Excel to share the document. When you do this, you have the incredibly annoying "feature" of tracking each individuals changes to the spreadsheet. To turn this on and off, in Excel you go to Tools then click on Share Workbook... From there you can cause all sorts of trouble by sharing it, and keeping track of each person's changes for however long you like. In your case, if this is shared and is the source of the problem, you can stop sharing, save the file, open it, share it again, and save again.

However, I do recommend if there is any concern about corruption, to copy and paste the contents into a new spreadsheet. So I think you did the right thing anyway...

 

[Jock1970]

Hi Guys,

Thanks for the suggestions.

I'm at home now, and the file is at work - I'll try you're tips out in the mormning and see if they make any difference.

Cheers,

Jock

 

[GrayShades]

Check the last used cell for each sheet. Hit CTRL + END. If it is wildly different than what you know to be the limits of your data, it might be filled with extra stuff. To delete, go to one column to the right of the last column, use CTRL + SHIFT + right arrow, to select all these cells, then delete. Now go to the row below the last row of data. CTRL + SHIFT + down arrow and delete. Then try the CTRL + END combo again.

Other considerations:

Do you have named ranges not being used? Try Name Manager to help clean up the process.

Do you have conditional formatting on empty cells?

If you import files from databases, the Excel file tends to balloon. Check for unnecessary stuff.

Do you use many formulas? Are they calculating in the proper sequence (on the sheet, and from sheet to sheet)?

Software: XL2002 on Win2K
Humanware: Older than dirt

 

[Jock1970]

Hi Guys,

I've checked the workbook and it is not shared.

There are no 'conditional formatting' parameters other than those in the VB script above. This does however seem to apply to the whole worksheet - how do I change this ? (not that it is the problem as it also applies to the whole worksheet in the 49kb version).

Also, when I open the workbook on another PC a message pops up about security and macros and no signature etc. The formatting in the VB script then does not work. I tried making my own digital signature but the other PC doesn't accept this. Any workaround for this ?

Cheers,

Jock

 

[klklkl]

I think there is another feature that keeps track of the document when several other people are working on it, but I don't know how to go into that and change it. I've only dealt with shared workbooks and their bloated sizes...

As for the security message on the other computer, that's a "feature" in Excel that tries to keep you from executing viruses in the form of macros. It looks to see if there is a digital signature signed by a trusted CA attached to the workbook. If it finds one, it lets you through, no questions asked. If it doesn't it then it either doesn't let the VB stuff execute (which is what you're seeing) or prompts you to let it run or not (which you're not seeing). I think there's a setting in Excel to change this so you're warned and then prompted to run the script or not.

 

[Jock1970]

Thanks for your reply klklkl,
I found earlier today that if I changed the macro security setting to medium (from high) it then asked whether to enable macros when the file is opened.
Cheers,

Jock