excel workbook 250 meg in size for no reason

[kevinu]

I have a workbook that I made from another workbook. The original was 168 meg and had 20 tabs and analyzed a crap ton of data. The new one is only 6 tabs and looks at the exact same data but its size is 255 meg? any ideas whats going on? I checked and there isnt extra rows or columns being used, No external links... I did make this one by simply deleting tabs from the old one and saving it as a new program. Any ideas? I'd attach a copy but the size is too big.

[Logit]

.

#1:

Paste this macro into a COPY ONLY OF THE WORKBOOK and run it.

Please Login or Register  to view this content.

It looks at all the empty cells/row below the last row of visible data, on each sheet, and clears out any extraneous data that is present but it not visible.
If it works on the COPY OF THE WORKBOOK, you can simply change the name of the copy to the original name and proceed.


#2:

If the above doesn't work ... click on a cell in sheet 1 / col A. Then simultaneously press CTRL / DOWN ARROW. This will take you to the bottom of the sheet.

Highlight the bottom 5 or 10 rows by clicking on the row numbers on the left side.

Now without clicking on any cells, scroll back up to the first blank row below your visible data. Hold down the SHIFT key and click on that first empty row.
Now, right click the highlighted rows, select DELETE.

These instructions in #2 do the same thing as #1 instructions, except you are manually performing the actions ... AND ... sometimes the macro in #1 doesn't
work for all workbooks.

[kevinu]

Thanks for the idea. I had to do it manually but it didnt help. All sheets only go down to the bottom used row and column. Still 250 meg. Any other ideas?

[romperstomper]

Is the data being analysed stored in the workbook, or sourced from elsewhere?

[Logit]

.
Try this ON A COPY FIRST.

This would be the last suggestion I have before thinking you would have to re-code your workbook from
the start.


Someone else may have additional suggestions.

[kevinu]

its being sourced on the program itself. The weird thing is the original program is 165 meg in size. All i did was delete about 15 tabs that I didnt need anymore and and added about 25 simple graphs and now its 250 meg. So I know it shouldnt be bigger because of the stored data because the original program had the same data and was smaller.

[BMV]

kevinu, It could be formatting, Objects and file destruction. Try this code
https://www.planetaexcel.ru/forum/in...#message617994 or If you can upload file to any filesharing resources and allow us to download.

[jeffreybrown]

Also, sometimes, simply saving your workbook as a .xlsb can cut the size in half. Worth a try.

[BMV]

jeffreybrown, Sorry, but it's not solution as a rule.

[jeffreybrown]

jeffreybrown, Sorry, but it's not solution as a rule.

That's why I did say "sometimes". And whether is works in this case, it might be helpful at other times. Just sharing.

[romperstomper]

Regular charts or pivot charts?

[kevinu]

LOL.... the one program wouldnt work, something about not being trusted. I tried the web link that had a script to run and it ran fine but the program INCREASED from 250 to 265 meg now So at least i know its not me now

the charts are regular charts not pivot tables. I just selected a range and hit add chart.

If anyone is interested I can put it on a google drive to give access. I can just rewrite it but now im curious whats going on and how to fix it if it happens again in the future.

I just tried saving it as a an .xlsb and it did reduce it to 38 meg. Does that affect the macros and VB programs in it by making it .xlsb?

[kevinu]

I tried deleting one tab at a time and the largest change came from the tab that holds all the data. Its 630067 rows long BUT the program is 250 meg with it empty. I can understand if its full of data. Is it storing the 630067 rows as if their full even when their empty? How it works is i import the data then delete it when done. I've already tried selecting the empty rows and deleting them or using clear all and the scroll bar still goes to 630067 even though the cells are empty. Could this be the issue?

[jeffreybrown]

Yes, that is an issue. If the cells are blank, the last row should not go to 630067.

Saving the doc as .xlsb will not impact the VBA from my experience.

To make a last cell, try this.

http://dmcritchie.mvps.org/excel/lastcell.htm

Sub makelastcell()

[Logit]

I tried deleting one tab at a time and the largest change came from the tab that holds all the data. Its 630067 rows long BUT the program is 250 meg with it empty. I can understand if its full of data. Is it storing the 630067 rows as if their full even when their empty? How it works is i import the data then delete it when done. I've already tried selecting the empty rows and deleting them or using clear all and the scroll bar still goes to 630067 even though the cells are empty. Could this be the issue?

This is the same issue I outlined in my response to you. I have found that oftentimes, CLEAR CONTENTS does not rid the workbook of the "invisible garbage data" that exists in those "blank" cells.
The only thing you can do is to highlight all of those "blank" rows ... then DELETE.

[kevinu]

Guys thanks for all the advise, I got it to shrink down. You were all correct I had to delete / clear all on the unused cells then save it and it shrunk to 65 meg. I also had numbers marking every 17000 rows to help me find data and thats what made it keep all 650k rows. I took those off and deleted / cleared all and that was the trick.

[jeffreybrown]

Hi kevinu,

Glad you got it all sorted out and have found something you can work with. Thank you for the feedback and we are all happy to help where we can.

[Logit]

.
Great ! Cheers !!!