I have a spreadsheet that I am creating with a lot of VBA associated. I save the file with a new version number after making revisions. I noticed about 40 revisions ago that my file size jumped from 1.1MB to 4.8MB between two versions. After much research I believe I know why the size increased but I can't seem to find a way to get rid of the bloat.
I was adding conditional cell protection to the workbook which would ensure all cells were Locked on all sheets before Unlocking the appropriate cells for user input. I created a constant AllCells with a string of "A1:EE1200" which would cover the entire range of any cells in use on any of the worksheets. I iterated through the following code for each worksheet:
With Sheets(sht) .Range(AllCells).Locked = True .EnableSelection = xlUnlockedCells .Protect UserInterfaceOnly:=True, DrawingObjects:=True, Contents:=True, Scenarios:=True End With
In the next version I found WorkSheet.UsedRange() and subsequently modified:
.Range(AllCells).Locked = True
.UsedRange.Locked = True
Which is obviously the better way to go, but in researching the bloat I found that by pressing Ctrl+End on my worksheets it would identify EE1200 as my last used cell.
I have selected all rows from 1200 up to the last row in use on each sheet and deleted the selected rows. I have selected all cells from EE1200 up to the last cell used on each sheet and pressed delete. With the same range I also selected Clear All from the menu.
Nothing I have done has changed the fact that Ctrl+End finds EE1200 on all my sheets.
If anyone has any ideas I would greatly appreciate the learning.
I wouldn't say that I have fully understood the issues but I have found that formatting an entire sheet does not cause the used range to extend and therefore the file size barely changes. I suppose it makes sense if one assumes the formatting is only actually applied to cells that are currently visible on screen so it is only the rules that are stored. If there is no end-point (your cell EE1200), there is nothing to store.