Sudden explosion in size of sheet in workbook

AB1984

New Member
Joined
May 12, 2016
Messages
17
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
.
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.
 
Upvote 0
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.
 
Upvote 0
.
Hmmm .... the macro works well here without errors.

Perhaps you have a corrupted workbook at this time.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top