Bloated Excel file

Question

Someone send me an Excel file that's 10MB, and thus causes us email problems. When .zip'ed it goes to under 1MB, so presumably there's something wrong with the Excel sheet but is there any way to compress this somehow?

I have a range of versions of Excel available to me by the way.

Answer 1

If there is space that can be 'reclaimed', there are several different things you can try:

1. Reset all the Used Ranges in each workbook. This can be done using a VBA method like this:

   Public Sub ResetAllLastCells()

   Dim wks As Worksheet
   Dim i As Long
   
   For Each wks In ActiveWorkbook.Worksheets
       i = wks.UsedRange.Rows.Count
   Next wks
   

   End Sub

2. Remove any unwanted custom styles. I use a VBA method as below to remove all custom styles:

   Public Sub StyleKill()

   Dim s As Style
   
   For Each s In ActiveWorkbook.Styles
       If Not s.BuiltIn Then
           s.Delete
       End If
   Next s
   

   End Sub

3. If there is VBA code within the workbook, try saving it decomiled. You can do this by opening the workbook, open the VBE, make a small change (add white space or something) and then, without compiling the project, save the workbook. This can dramatically reduce the file size.

I'm sure there are other ways too but these are my tried and tested ways. In one workbook I have this consistently reduces the file size from ~15mb to 10mb.