Sudden explosion in size of sheet in workbook

[AB1984]

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:

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]

.
Make a COPY of your workbook.


On the copy only, apply this macro :

[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]

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]

.
Hmmm .... the macro works well here without errors.

Perhaps you have a corrupted workbook at this time.