I have a report, it has some data around 1300 rows, file size is around 400k.
One column is supplier name. I splite the file by company name.

I copy the data to a worksheet("TEMP") and calculate something.
after that, I copy to a new workbook.

after the process, the file size of the report change to 27.6M
I find the reason is come from sheet TEMP.
If I delete all rows in TEMP, the file size will be changed to normal.
I am sure I have clear the data in Sheet TEMP.

actually, I can use macro to delete the rows before end sub.
but I want to know the reason and delete all row spend a lot of time.

Thanks in advance

From menu use ClearFormats under Edit|Clear and see

If this post helps click Yes
--------------
Jacob Skaria


"leungkong" wrote:

I have a report, it has some data around 1300 rows, file size is around 400k.
One column is supplier name. I splite the file by company name.

I copy the data to a worksheet("TEMP") and calculate something.
after that, I copy to a new workbook.

after the process, the file size of the report change to 27.6M
I find the reason is come from sheet TEMP.
If I delete all rows in TEMP, the file size will be changed to normal.
I am sure I have clear the data in Sheet TEMP.

actually, I can use macro to delete the rows before end sub.
but I want to know the reason and delete all row spend a lot of time.

Thanks in advance

You have a lot of formulas in the new book pointing to the original workbook
(or other workbooks). Before copying the Temp sheet I would copy the sheet
and then paste it back with pastespecial using values to remove the formulas.
Then the new book will end up being much smaller.

"Jacob Skaria" wrote:

From menu use ClearFormats under Edit|Clear and see

If this post helps click Yes
--------------
Jacob Skaria


"leungkong" wrote:

I have a report, it has some data around 1300 rows, file size is around 400k.
One column is supplier name. I splite the file by company name.

I copy the data to a worksheet("TEMP") and calculate something.
after that, I copy to a new workbook.

after the process, the file size of the report change to 27.6M
I find the reason is come from sheet TEMP.
If I delete all rows in TEMP, the file size will be changed to normal.
I am sure I have clear the data in Sheet TEMP.

actually, I can use macro to delete the rows before end sub.
but I want to know the reason and delete all row spend a lot of time.

Thanks in advance

On the bloated sheet, hit Ctrl+End and that will take you to the end of the
used range. Almost guaranteed, you will go very far to the right, and very
far down, from where you expect to be. This is the 'used range'. Select the
columns that are not being used, and go left until you get to the end of your
actual data. Delete this stuff. Again, select rows that are not being used,
and go up until you get to the end of your actual data. Delete this stuff.

Save and reopen you file. It should be fine now. Check the actual file
size to verify.

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"joel" wrote:

You have a lot of formulas in the new book pointing to the original workbook
(or other workbooks). Before copying the Temp sheet I would copy the sheet
and then paste it back with pastespecial using values to remove the formulas.
Then the new book will end up being much smaller.

"Jacob Skaria" wrote:

From menu use ClearFormats under Edit|Clear and see

If this post helps click Yes
--------------
Jacob Skaria


"leungkong" wrote:

I have a report, it has some data around 1300 rows, file size is around 400k.
One column is supplier name. I splite the file by company name.

I copy the data to a worksheet("TEMP") and calculate something.
after that, I copy to a new workbook.

after the process, the file size of the report change to 27.6M
I find the reason is come from sheet TEMP.
If I delete all rows in TEMP, the file size will be changed to normal.
I am sure I have clear the data in Sheet TEMP.

actually, I can use macro to delete the rows before end sub.
but I want to know the reason and delete all row spend a lot of time.

Thanks in advance