How do I eliminate unneeded (hidden) info?

MPW

Well-known Member
Joined
Oct 7, 2009
Messages
532
Office Version
  1. 365
Platform
  1. Windows
Hi All,

I have a 2003 Workbook that is around 7 mb. It used to be half of that but it jumped up in size which makes it harder to email, etc.
On the front page it would go out past the (known) data and I was able to reset it to the correct place but on the 2nd sheet it would go to IV 65536 without fail. I used VBA as well as physically deleting and\or clearing rows and columns but to no avail.

I decided to start eliminating portions of the workbook to see if I could find where the size was coming from.

1. I deleted Regular formatting
2. I deleted Conditional formatting
3. Made everything the same font name and size, etc
4. I deleted all VBA Code.
5. Last but not least I deleted/cleared all data from all the sheets

Between each change I would save and close the workbook and check the size in Explorer.
Each deletion brought down the size, however after finishing step 5, I had a blank workbook with 3 sheets and it was still over 1.4 meg!

The next step was to remove the 2nd sheet. I did this and it went from 1.4 meg to 25 kb.

At this point I have been coping all the info into notepad and the from notepad into a new workbook. While this is working I still would like to know if anyone has a better answer.

MPW
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

MPW

Well-known Member
Joined
Oct 7, 2009
Messages
532
Office Version
  1. 365
Platform
  1. Windows
Thanks dave3009,

I did see that thread a while back and have used some of the ideas. Copying all the data from the sheets into notepad seems to work well enough (except for all the time to reformat everything after it is pasted into a new workbook) and I can do the same with the vba code, however I still do not have a good alternate way to import all the userforms without just exporting them. I did try the html method (without good success) so I guess I am stuck with the basic export/import method or the long winded method of trying to recreate the userforms manually.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,608
Messages
5,597,143
Members
414,129
Latest member
mr vba

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