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

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
2,040
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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.

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

Forum statistics

Threads
1,223,238
Messages
6,170,939
Members
452,368
Latest member
jayp2104

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
Back
Top