Sudden explosion in size of sheet in workbook

AB1984

New Member
So, I've just joined a new team, and we make heavy use of Excel templates for data entry from our various clients.
However, there's a lot edits that get made to these spreadsheets and occasionally and without warning, or even being informed, the file size suddenly increases by inexplicable amounts.

I've tried the old rename to zip and explore method and found that one particular sheet went from 45kb uncompressed size up to 75mb.
It was sheet7.xml within the renamed zip (I don't think that's relevant).

Now there is one entry in the xml:
Code:
row r="97" ht="27" hidden="1" customHeight="1" x14ac:dyDescent="0.2"
Repeated for every possible row from 97 down.

Is there a way to remove this? I tried just deleting these extra rows (all 1.5mil of them) and it corrupted the file :(

My team suspects it's do with hiding and un-hiding cells repeatedly. But any advice would be really appreciated. Or maybe insight into why it happens
 

Logit

Well-known Member
.
Make a COPY of your workbook.


On the copy only, apply this macro :

Code:
[B]Sub LipoSuction()[/B]
'JBeaucaire (8/3/2009)
Dim LR As Long, LC As Long
Dim ws As Worksheet
 
For Each ws In Worksheets
    LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row + 1
    LC = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column + 1
 
    'Clear everything below column A last cell and row 1 last cell
        ws.Range(ws.Cells(1, LC), ws.Cells(ws.Rows.Count, ws.Columns.Count)).Delete
        ws.Range(ws.Cells(LR, 1), ws.Cells(ws.Rows.Count, ws.Columns.Count)).Delete
Next ws
 
End Sub

If the above works well on the copy ... apply it to the original.
 

AB1984

New Member
Thanks for that Logit,
It 'application-defined or object-defined' errored out though :(
We do make use of a lot of hidden columns/rows in order to make things pretty, as well as additional cell protections. So this might be causing problems as well, with actually tidying things up.

It'd be interesting to know if anyone else has come across this issue, and what the root cause is. Then we can just avoid doing that.
 

Logit

Well-known Member
.
Hmmm .... the macro works well here without errors.

Perhaps you have a corrupted workbook at this time.
 

Some videos you may like

This Week's Hot Topics

Top