Home

XLSM suddenly increased in size by four times

%3CLINGO-SUB%20id%3D%22lingo-sub-680014%22%20slang%3D%22en-US%22%3EXLSM%20suddenly%20increased%20in%20size%20by%20four%20times%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-680014%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20spreadsheet%20that%20I%20am%20creating%20with%20a%20lot%20of%20VBA%20associated.%26nbsp%3B%20I%20save%20the%20file%20with%20a%20new%20version%20number%20after%20making%20revisions.%26nbsp%3B%20I%20noticed%20about%2040%20revisions%20ago%20that%20my%20file%20size%20jumped%20from%201.1MB%20to%204.8MB%20between%20two%20versions.%26nbsp%3B%20After%20much%20research%20I%20believe%20I%20know%20why%20the%20size%20increased%20but%20I%20can't%20seem%20to%20find%20a%20way%20to%20get%20rid%20of%20the%20bloat.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20adding%20conditional%20cell%20protection%20to%20the%20workbook%20which%20would%20ensure%20all%20cells%20were%20Locked%20on%20all%20sheets%20before%20Unlocking%20the%20appropriate%20cells%20for%20user%20input.%26nbsp%3B%20I%20created%20a%20constant%20AllCells%20with%20a%20string%20of%20%22A1%3AEE1200%22%20which%20would%20cover%20the%20entire%20range%20of%20any%20cells%20in%20use%20on%20any%20of%20the%20worksheets.%26nbsp%3B%20I%20iterated%20through%20the%20following%20code%20for%20each%20worksheet%3A%3C%2FP%3E%3CH6%20id%3D%22toc-hId-980431937%22%20id%3D%22toc-hId-980431939%22%20id%3D%22toc-hId-980431939%22%20id%3D%22toc-hId-980431939%22%20id%3D%22toc-hId-980431939%22%20id%3D%22toc-hId-980431939%22%20id%3D%22toc-hId-980431939%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20With%20Sheets(sht)%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20.Range(AllCells).Locked%20%3D%20True%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20.EnableSelection%20%3D%20xlUnlockedCells%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20.Protect%20UserInterfaceOnly%3A%3DTrue%2C%20DrawingObjects%3A%3DTrue%2C%20Contents%3A%3DTrue%2C%20Scenarios%3A%3DTrue%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20End%20With%3C%2FH6%3E%3CP%3EIn%20the%20next%20version%20I%20found%20WorkSheet.UsedRange()%26nbsp%3Band%20subsequently%20modified%3A%3C%2FP%3E%3CH6%20id%3D%22toc-hId--1571725024%22%20id%3D%22toc-hId--1571725022%22%20id%3D%22toc-hId--1571725022%22%20id%3D%22toc-hId--1571725022%22%20id%3D%22toc-hId--1571725022%22%20id%3D%22toc-hId--1571725022%22%20id%3D%22toc-hId--1571725022%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20.Range(AllCells).Locked%20%3D%20True%3C%2FH6%3E%3CP%3Eto%3C%2FP%3E%3CH6%20id%3D%22toc-hId-171085311%22%20id%3D%22toc-hId-171085313%22%20id%3D%22toc-hId-171085313%22%20id%3D%22toc-hId-171085313%22%20id%3D%22toc-hId-171085313%22%20id%3D%22toc-hId-171085313%22%20id%3D%22toc-hId-171085313%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20.UsedRange.Locked%20%3D%20True%3C%2FH6%3E%3CP%3EWhich%20is%20obviously%20the%20better%20way%20to%20go%2C%20but%20in%20researching%20the%20bloat%20I%20found%20that%20by%20pressing%20Ctrl%2BEnd%20on%20my%20worksheets%20it%20would%20identify%20EE1200%20as%20my%20last%20used%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20selected%20all%20rows%20from%26nbsp%3B1200%20up%20to%20the%20last%20row%20in%20use%20on%20each%20sheet%20and%20deleted%20the%20selected%26nbsp%3Brows.%26nbsp%3B%20I%20have%20selected%20all%20cells%20from%20EE1200%20up%20to%20the%20last%20cell%20used%20on%20each%20sheet%20and%20pressed%20delete.%26nbsp%3B%20With%20the%20same%20range%20I%20also%20selected%20Clear%20All%20from%20the%20menu.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENothing%20I%20have%20done%20has%20changed%20the%20fact%20that%20Ctrl%2BEnd%20finds%20EE1200%20on%20all%20my%20sheets.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20anyone%20has%20any%20ideas%20I%20would%20greatly%20appreciate%20the%20learning.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETIA...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-680014%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-680066%22%20slang%3D%22en-US%22%3ERe%3A%20XLSM%20suddenly%20increased%20in%20size%20by%20four%20times%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-680066%22%20slang%3D%22en-US%22%3EHi%20%3CBR%20%2F%3E%3CBR%20%2F%3EYou%20need%20to%20delete%20the%20rows%20and%20the%20columns%2C%20not%20just%20press%20delete.%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20that%20fails%20to%20work%20then%20you%20may%20be%20able%20to%20use%20Inquire%20(file%20File%20%26gt%3B%20Options%20%26gt%3B%20Addins%20%26gt%3B%20COM%20Addins).%20That%20has%20a%20button%20called%20Clear%20Excess%20Cell%20formats%20that%20may%20do%20the%20trick%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-680067%22%20slang%3D%22en-US%22%3ERe%3A%20XLSM%20suddenly%20increased%20in%20size%20by%20four%20times%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-680067%22%20slang%3D%22en-US%22%3EAlso%20-extra%20note%20-%20After%20deleting%20rows%20and%20columns%20you%20need%20to%20close%20and%20re-open%20the%20file%20before%20trying%20Ctrl%2BEnd%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-680961%22%20slang%3D%22en-US%22%3ERe%3A%20XLSM%20suddenly%20increased%20in%20size%20by%20four%20times%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-680961%22%20slang%3D%22en-US%22%3E%3CP%3EI%20did%20delete%20the%20rows%20using%20the%20proper%20method%20but%20rechecked%20before%20saving%20the%20spreadsheet.%26nbsp%3B%20Saving%20and%20closing%20before%20pressing%20Ctrl%2BEnd%20again%20seemed%20to%20have%20resolved%20the%20problem.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20you%20help%20really%20appreciate%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-681369%22%20slang%3D%22en-US%22%3ERe%3A%20XLSM%20suddenly%20increased%20in%20size%20by%20four%20times%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-681369%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F357347%22%20target%3D%22_blank%22%3E%40mpgAAAA%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20wouldn't%20say%20that%20I%20have%20fully%20understood%20the%20issues%20but%20I%20have%20found%20that%20formatting%20an%20entire%20sheet%20does%20not%20cause%20the%20used%20range%20to%20extend%20and%20therefore%20the%20file%20size%20barely%20changes.%26nbsp%3B%20I%20suppose%20it%20makes%20sense%20if%20one%20assumes%20the%20formatting%20is%20only%20actually%20applied%26nbsp%3B%20to%20cells%20that%20are%20currently%20visible%20on%20screen%20so%20it%20is%20only%20the%20rules%20that%20are%20stored.%26nbsp%3B%20If%20there%20is%20no%20end-point%20(your%20cell%20EE1200)%2C%20there%20is%20nothing%20to%20store.%3C%2FP%3E%3C%2FLINGO-BODY%3E
mpgAAAA
New Contributor

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

to

        .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.

 

TIA...

4 Replies
Hi

You need to delete the rows and the columns, not just press delete.

If that fails to work then you may be able to use Inquire (file File > Options > Addins > COM Addins). That has a button called Clear Excess Cell formats that may do the trick
Also -extra note - After deleting rows and columns you need to close and re-open the file before trying Ctrl+End

I did delete the rows using the proper method but rechecked before saving the spreadsheet.  Saving and closing before pressing Ctrl+End again seemed to have resolved the problem.

 

Thanks for you help really appreciate it.

 

@Wyn Hopkins

@mpgAAAA 

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.

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies