File size issue

I have two documents, one with a macro in and one with the data in that i manipulate with the macro and send out to other people.

The file in which i use the macro on is aprox 5mb to start with

when the macro is used it increases to almost 25mb and all it has done is added around 8000 cells of data and deleted 37,000 cells on the original 44,827 cells of data (4 new tabs are created and 3 tabs are deleted)

why does it increase so much in size? even if i copied everything on the original sheet and didnt delete anything 4x i still should only end up with a MAXIMUM of 20mb, why after deleting loads of cells and re-arranging/filtering them does it increase by almost 5x? Is there a possible way to decrease the size of the data before saving it as the excel file?

thanks in advance

Unwanted/unexpected large file sizes are usually the result of too much formatting or too much worksheet functions.

I would check to see if entire(max size) rows or columns are getting formatted. If they are, then adjust your macros to format the least amount and delete the unused cells.

is there any way to see the extra cells that are being saved? or a way to delete all unused rows/columns?

i did the macro again with a different data range and got a similar figure for the size... im thinking that the columns are fine but the rows are not...

the sheet im using the macro on can change in size every week so i cannot put a definitive figure on how many rows i need to go up to, id prefer to have the option to select all rows with no data in it to delete if thats possible?

thanks again

is it possible to select this data via macro? the issue being i select columns like (A:A) which im assuming creates all that unnecessary data. is it possible to select last used row via macro?

also CTRL + SHIFT + Down will highlight all rows until the end, ctrl+down just takes you to the last row =)

Originally Posted by DGagnon

and to delete all rows bellow that somethign like this:

Please Login or Register  to view this content.

once the term "last row" is defined can i use it elsewhere, for example if i wanted to select all the data in a column i could use something like

Please Login or Register  to view this content.

the issue comes from this part in the macro

Please Login or Register  to view this content.

as you can see selecting lots of whole columns seemed like an easy way to start up the macro but caused a large file size in the end.
maybe setting something like...

Please Login or Register  to view this content.

would prove to be a better way of copying the data i need without leaving loads of space unaccounted for.

not filled in no... Column B is a reference number though so however many rows are in column B is the number of rows i want to copy...

the sheet is used as a data logger and if the issue/data doesnt require a certain column to fill in then that is ok however column B will always be filled in.

obviously week to week the number of rows may change so i dont want just a fixed value like.... (A1:A1000)

thank you! this worked =) thanks very much