Excel file too large

Just a few thoughts to speed up:
Have you deleted columns/rows that contain no data. Press ctrl-end on each sheet to see if you have blank rows/columns. If so delete them (don't just clear them). When you save, your file should be smaller.

Have you considered splitting it into separate (linked) files?

Have you reviewed the formulas to see if data from a number of columns/rows can be condensed into one?

Is your calculation set to manual?

The file is from another dept. and I have yet to see it's format due to the length of time it takes to open, too impatient! But once I get it open I will be sure to check those items and see if I can do anything with them.

Thanks!

see if any of this helps:

http://www.ozgrid.com/Excel/ExcelProblems.htm

In addtion to what already have been mention another good idea is to rebuild the workbook with a tool such as Excel Workbook Rebuilder by Andrew Baker. It's not free but it may be a small cost compared to to it manually.

Check it out here:
http://www.vbusers.com/commercial/Rebuild.asp

and You can also download an evaluation version of it.

(No, I have no commercial interest in it - I´m only a pleased customer :wink: )

Good luck!
Dennis

From experience, three of the main contributors to file size bloat:
1. Saving the file in a lower version.
2. Embedded graphics.
3. The actual used range far exceeds the visible used range.

Some of the following may be relevant;

http://cpearson.com/excel/optimize.htm

Some tips on overcoming slow response (David McRitchie)
http://www.mvps.org/dmcritchie/excel/slowresp.htm

Opening/Saving Bottlenecks (Charles Williams)
http://www.decisionmodels.com/optspeedd.htm#OpSave
http://www.decisionmodels.com/optspeed.htm

Optimizing for Size and Speed
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/office97/html/web/013.asp

Random collection of various statements/comments from people on the Net (I collect these from time to time):

Charles Williams
www.DecisionModels.com

- make the range referred to in Vlookup/Index as small as possible
- make sure all your named ranges and all other ranges refer to as small a range of cells as possible
- make sure your used range is not excessive ( check with edit -->goto-->Special-->Lastcell)
- eliminate as much sparseness as possible (ie as few blank cells embedded in the used range as possible)
- use dynamic ranges if appropriate
- store frequently occurring "formula snippets" in named formulas
- use multi-cell array formulae to replace blocks of formulae where possible
- remove zero-sized objects
- switch off Change tracking for shared workbooks
- reduce the number of worksheets by merging several sheets into one
- Avoid references to large ranges on other worksheets, particularly references to blank or unused cells
- simplify formatting/reduce fonts etc
- avoid embedded graphics
- small savings are possible with short wide sheets rather than tall narrow sheets ( but less useable).

Chip Pearson

Set the calculation mode to manual unless you really need it set to automatic while your code executes.

Set Application.ScreenUpdating to False to prevent Excel from updating the screen while your code runs.

Make use of the With / End With construct whenever possible.

Avoid Variant and Object type variables whenever possible - declare variables with a specific type.

Don 't use the Select method and Selection object -- access a range
directly. E.g., instead of
Range("A1").Select
Selection.Value = 123
use
Range("A1").Value = 123

Sundry

You could put your macro's in a separate file and reduce file size that way.

Make sure that with conditional formatting you format only the range you want and not the whole row, column or sheet.

Don’t copy and paste images from Word into Excel - use Insert/Picture

Try reducing the amount of formatting you apply with the macro.

Try commenting out all the VBA code, save.

Try separating out the macro from the workbook you are actually having the macro work on (make them two separate workbooks).

Over time if you use the same workbook over and over, applying values, formats, copying worksheets, garbage will collect bloating the file size.

Macro Modules

You should keep macro modules at less than 64K in size. How do you tell the size - export the module and then look at the .bas file to see what the file size is.
Tom Ogilvy (Microsoft MVP)

The module size limitation isn't hard and fast, nor is it limited to any specific version of Excel. In fact I don't think it's ever been officially documented.However , it 's pretty well recognized among professional Excel developers that if you create code modules larger than 64KB you will very likely run into problems.
Rob Bovey, MCSE, MCSD, Excel MVP


HTH

Mike

tactps said:

Just a few thoughts to speed up:
Have you deleted columns/rows that contain no data. Press ctrl-end on each sheet to see if you have blank rows/columns. If so delete them (don't just clear them). When you save, your file should be smaller.

Have you considered splitting it into separate (linked) files?

Have you reviewed the formulas to see if data from a number of columns/rows can be condensed into one?

Is your calculation set to manual?

Click to expand...

The first tip worked great for me, thanks. File size went from > 17 Megs to 120K. Dunno how it got to that size in the first place.