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!
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,708
Sorry, didn't read your post in full and suggested what you tried!
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,028
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Have you done a CTRL + end on each sheet to see where the cursor goes to ??
You could also try to reduce the bloat by running this macro
VBA Code:
Public Sub MM1()
Dim ws As Worksheet
For Each ws In Worksheets
    ws.Activate
    ActiveSheet.UsedRange
Next ws
End Sub
 

roscoe

Well-known Member
Joined
Jun 4, 2002
Messages
1,016
Thanks...I guess I don't follow what ActiveSheet.UsedRange actually does. Does it highlight the used range? Not sure how that will reduce bloat (but very interested in learning how)!
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,028
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

It simply resets the usedRange on the sheet to the last used cell !
So, if the used range goes waaay down the worksheet, into miles of empty space...the code will bring it back to where the actual last cell is......It doesn't select OR delete any data !
You have to save and close for the changes to take effect
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,028
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
There is a pretty good explanation of it here...
.
 

roscoe

Well-known Member
Joined
Jun 4, 2002
Messages
1,016

ADVERTISEMENT

Thanks. Does formatting a cell extend the range or does it actually have to have content? Still trying to figure out what casued the problem..

(I fixed it before I got this tip...was very painful)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,331
Office Version
  1. 365
Platform
  1. Windows
The used range covers all formatted cells, regardless of if they are empty or not.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,028
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
So, can you share how you fixed it ? That way others may learn from the experience....:):)
 

roscoe

Well-known Member
Joined
Jun 4, 2002
Messages
1,016
I exported my named ranges (I had a bunch that were very dynamic and took a long time to develop), deleted the offending page, then rebuilt and then pasted the named ranges back in over the ones that had errors.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,144
Messages
5,622,993
Members
415,944
Latest member
loribear180

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