Wookiee
Active Member
- Joined
- Nov 27, 2012
- Messages
- 429
- Office Version
- 365
- 2019
- Platform
- Windows
File bloat happens from time to time and I'm usually able to fix such files by manually recreating them and making sure to minimize the number of different cell formats.
I just went through that process on a workbook of mine which had bloated up to a ridiculous size of 4MB (considering it contained only 3 worksheets with probably less than 200 cells containing formulas). I copied the used range of one worksheet and pasted into a new workbook. When I saved the new file at that point, the size was around 69KB. I then revised the macro code which imports data and adds it in a new sheet each month (making sure to add code to clear the contents of all unused cells before saving)*. I should note that the file is in .xlsb format (which I've always found to have the smallest file size of any of the 2007+ formats). Alas, after I ran the code, the file went up to 2.28MB.
I was briefly thinking that it might have something to do with the fact that my macro opens an old format Excel file (.xls), copies the entire sheet then pastes it into a new sheet in my report workbook. It then does some sorting and calculating, but (with the exception of subtotals) I either clear formulas from cells or convert them to their actual values. I doubted that was the issue because I have a number of files which I use on a daily basis which copy .xls files into .xlsb (and with a much greater volume of data and formulas), but they rarely go over 600KB in size.
I usually email this report to my boss every month, but I consider it a breach of email etiquette to email files of such size. I even considered including a hyperlink to the file so he could open it himself, but since it contains my co-worker's employee information, I have it saved on my personal drive.
Does anyone care to venture a hypothesis as to why my file is bloating up faster than Violet Beauregard in Willy Wonka's Chocolate Factory?
*Below is the code I wrote to clear cell contents outside of my used range of cells:
I just went through that process on a workbook of mine which had bloated up to a ridiculous size of 4MB (considering it contained only 3 worksheets with probably less than 200 cells containing formulas). I copied the used range of one worksheet and pasted into a new workbook. When I saved the new file at that point, the size was around 69KB. I then revised the macro code which imports data and adds it in a new sheet each month (making sure to add code to clear the contents of all unused cells before saving)*. I should note that the file is in .xlsb format (which I've always found to have the smallest file size of any of the 2007+ formats). Alas, after I ran the code, the file went up to 2.28MB.
I was briefly thinking that it might have something to do with the fact that my macro opens an old format Excel file (.xls), copies the entire sheet then pastes it into a new sheet in my report workbook. It then does some sorting and calculating, but (with the exception of subtotals) I either clear formulas from cells or convert them to their actual values. I doubted that was the issue because I have a number of files which I use on a daily basis which copy .xls files into .xlsb (and with a much greater volume of data and formulas), but they rarely go over 600KB in size.
I usually email this report to my boss every month, but I consider it a breach of email etiquette to email files of such size. I even considered including a hyperlink to the file so he could open it himself, but since it contains my co-worker's employee information, I have it saved on my personal drive.
Does anyone care to venture a hypothesis as to why my file is bloating up faster than Violet Beauregard in Willy Wonka's Chocolate Factory?
*Below is the code I wrote to clear cell contents outside of my used range of cells:
Rich (BB code):
'Clear Contents And Formats Of Superfluous Cells And Hide Extraneous Worksheet Space
With Range(Cells(1, 14), Cells(Rows.Count, Columns.Count))
.Clear
.EntireColumn.Hidden = True
End With
With Range(Cells(lngLastRow + 4, 1), Cells(Rows.Count, Columns.Count))
.Clear
.EntireRow.Hidden = True
End With
