Solved: reducing file size

[maksinx]

hello everyone,

i have an excel file which is about 1.5mb and has got afew macros and validation.It is also being used by 2-3 people to share.
is there anything i can reduce the file size and make it faster.

file has consist of 32 columns and 400 rows of data.

thanks in advance

[lucas]

Hard to tell without seeing it.....doesn't sound that big if it has many formula's and macro's......

[piercedgeek]

The only thing that comes to mind without seeing the file is that often times(at least often where I work, grrr...) people will format a block of data, rather than the entire column. Depending on the size of the block, this can inflate the file size several megs over what it needs to be. But as was said above, we really don't know for sure without seeing the file

[maksinx]

piercedgeek

can you explain abit more, i didnt quite understand what you mean.

i also add the file for your intention to check, in addition to that once i add validation it reduces its speed.

please advice if you have any comments.

thanks

[piercedgeek]

Sure thing, as an example, in your file, the data appears to stop at page 8 (row 447) but it's formatted as white down to page 427 (row 19,320!)
The best way to format excel is to click on the entire column or row (by clicking the letter or number), and NOT by highlighting a large block of cells. In the case of wanting a 'header' to be a different color, but the rest of the sheet to be one color, it works best to click the box in the very top left corner (left of A and above 1), it will highlight every cell in the workbook. Apply the color you want most of the book to be.
Then click on 1, and apply the color of your header.

Why:
I'm not 100% sure of the exact technical workings behind it, but based on what I've seen and what I know about computers, doing formatting of any sort (colors, font, borders) to an entire row or column excel will take up what we'll call 1 'storage slot' remembering: "Row 1 bold", but if you make 50 cells on row 1 bold, each cell uses its own 'storage slot' remembering: "Cell A1 bold", "Cell A2 bold", "Cell A3 bold" and so on.

In small numbers this isn't an issue, but just think of the difference when it's 19,000 rows long, and 42 columns wide (798,000 individual cells!!!)

As far as cleaning up this book without changing the data in it,
1) click on 448 to highlight the first blank row
2) drag the scroll bar to the bottom
3) hold shift and click on 19320
4) right click, and delete

Original file size: 1381 KB
New size: 229 KB

I hope I've explained this well, if not let me know and I'll think of another way to say it

[maksinx]

Dear piercegeek,

i have done as you explained, and drop to nearly 500kb which is acceptable,and working fine.( i had to keep some rows remaining colour,maybe thats why it didnt get down to 200kb)

thanks for your prompt help.

have a nice day.

regards

[piercedgeek]

No problem, glad I could help out!