How to reduce size of my excel file which is currently about 3.5 mbs

[omairhe]

Hey Excel Gurus,

Like the title suggests, I would like to reduce the size of my excel file. the file in previous times use to be around 1.5 mbs but is now more than double its size. The file is linked with dropbox, hence it is of utmost importance to maintain minimum possible size for fast transfers over the net.

One way of doing it is manually select all the data after last row's column A, and

1. Clear contents
2. Clear borders
3. Reset formatting
4. And finally to delete all unused rows

Trouble is I have more than 50 sheets and doing this job will require a lot of time. I was hoping if a Vba can run on all the sheets of my workbook and do the above in one go. Please take into consideration that the deletion, clearing the formatting, content and borders should begin at least after 10 empty rows at column A. Hence 10 empty rows should be kept unharmed for safety of the data.
Secondly the data in column A is all but formulas. And formulas in the end rows are blank, giving no output at all. These formulas will have to be deleted as well from the 11th row onwards.

I hope such a script can be made and save me a lot of time.

Thank you and will appreciate a lot.

 

[DanteAmor]

Run this macro, delete from row 11 to row 10,000 of all sheets, if you consider that your sheets have more than 10,000 rows change the number.
Execute a copy of your file.
When you delete the row, the formats are deleted.

Sub Macro5()
  Sheets.Select
  Rows("11:10000").Select
  Selection.Delete Shift:=xlUp
End Sub

 

[sandy666]

try to save as .xlsb