-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
How to compress a 10 MB file?
- Thread starter pkohli
- Start date
Damon Ostrander
MrExcel MVP
- Joined
- Feb 17, 2002
- Messages
- 4,239
Hi Pooja,
It depends. Is the file 10MB because it truly contains 10MB of data (this would correspond to roughly 100,000 cells of numbers)? Or is it 10MB because of "bloat" resulting from the usedrange problem?
If you are not familiar with the usedrange problem, there is a UsedRange property for each worksheet. This is the range of cells that Excel saves when the workbook is saved. It is supposed to the the smallest rectangular block of cells that contains every cell that has been changed--containing either data, formatting, validation, comments, etc. But there is a problem with Excel that it does not always collapse the usedrange properly when data are deleted from a worksheet. So Excel saves cells that don't need to be included in the file, and this increases the file size. You can check whether you have a problem by selecting any cell on a worksheet and typing Ctrl-end (using the end keyboard key). The selection will then go to the last cell (lower right corner) of the usedrange. If this cell is outside the range of the sheet containing data then you have usedrange bloat. There are numberous posts on this board regarding this problem and how to clean it up.
If you truly have 10MB of data your only option is to figure out a way to store less of it. I should mention that 100,000 cells of number sounds like a lot, but this is the number of cells in the range A1:CV1000 in one worksheet.
I hope this helps.
Keep Excelling.
Damon
It depends. Is the file 10MB because it truly contains 10MB of data (this would correspond to roughly 100,000 cells of numbers)? Or is it 10MB because of "bloat" resulting from the usedrange problem?
If you are not familiar with the usedrange problem, there is a UsedRange property for each worksheet. This is the range of cells that Excel saves when the workbook is saved. It is supposed to the the smallest rectangular block of cells that contains every cell that has been changed--containing either data, formatting, validation, comments, etc. But there is a problem with Excel that it does not always collapse the usedrange properly when data are deleted from a worksheet. So Excel saves cells that don't need to be included in the file, and this increases the file size. You can check whether you have a problem by selecting any cell on a worksheet and typing Ctrl-end (using the end keyboard key). The selection will then go to the last cell (lower right corner) of the usedrange. If this cell is outside the range of the sheet containing data then you have usedrange bloat. There are numberous posts on this board regarding this problem and how to clean it up.
If you truly have 10MB of data your only option is to figure out a way to store less of it. I should mention that 100,000 cells of number sounds like a lot, but this is the number of cells in the range A1:CV1000 in one worksheet.
I hope this helps.
Keep Excelling.
Damon
Upvote
0
NdNoviceHlp
Well-known Member
- Joined
- Nov 9, 2002
- Messages
- 3,619
That was a great read Damon and thanks for that. pkohli... I've noticed a file size reduction by Zipping a file, opening it, and then saving it again. Seems like the Zip sheds some extra stuff? Anyways, I'm sure Damon's route is the way to go but you might want to trial something simple first. HTH. Dave
Upvote
0
DominicB
Well-known Member
- Joined
- Oct 3, 2005
- Messages
- 1,569
Good morning pkohli
My free add-in (available via the link below) contains 3 file optimisation routines that combat the most popular reasons why files get bigger than is strictly necessary.
You could also have a look at this thread for a few tips :
http://www.mrexcel.com/board2/viewtopic.php?t=232835
HTH
DominicB
My free add-in (available via the link below) contains 3 file optimisation routines that combat the most popular reasons why files get bigger than is strictly necessary.
You could also have a look at this thread for a few tips :
http://www.mrexcel.com/board2/viewtopic.php?t=232835
HTH
DominicB
Upvote
0
