Workbook exploded in size...now unusable!?!?

roscoe

Well-known Member
Joined
Jun 4, 2002
Messages
1,016
I have a workbook that is normally about 3000KB in size. This weekend I was tinkering with it and it suddenly became 20,000KB is size (these are Windows explorer compressed sizes...One can only guess how much memory it's filling up). Pretty sure what sheet got corrupted because when on that sheet even simple arrow commands to move the selection takes dozens of seconds to react. Of course, that's the sheet to which I reference a ton of formulas and named ranges. To fix it, I copied the column headers to a new sheet, deleted every row on the sheet, then copied the headers back (the headers were the most critical part...the code filled in the data below it). That fixed the size problem, but destroyed all my formulas and named ranges (inserting ref! where the sheet was referenced). I lost hours repairing everything.

Happened again today...I'm at a loss as to what caused it and if there's an easier way to fix it

Other than the questions I alluded to above (1) how do I prevent this from happening in the future and (2) fastest way to fix it...

...Can I export my named ranges such that I can simply cut/paste them back in once I blast away the worksheet contnents?

Thanks!
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,703
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Ok, so was the last cell an issue on the offending sheet ??
 

Some videos you may like

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.

roscoe

Well-known Member
Joined
Jun 4, 2002
Messages
1,016
I have no idea. I fixed it my way before the other option was presented. But if .usedrange also include formatted cells I'm not sure that would have helped as I routinely format entire columns, not knowing how much data will be loaded into a given column. I may have to stop that practice if that's what is exploding my spreadsheet.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,703
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Yep...true that! !
Doing any entire row /column stuff will eat up your data. ..😎😎
 

Watch MrExcel Video

Forum statistics

Threads
1,122,456
Messages
5,596,234
Members
414,048
Latest member
wnied1

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
Top