Il est bien plus beau de savoir quelque chose de tout que de savoir tout d'une chose. [Blaise Pascal]

How to reduce Excel file size using a Macro

I found this VBA script to reduce Excel file size; in my case it reduces the original Excel file from 59.9 MB to 40 KB… What a shrink J

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim myLastRow As Long
Dim myLastCol As Long
Dim wks As Worksheet
Dim dummyRng As Range
For Each wks In ActiveWorkbook.Worksheets
  With wks
    myLastRow = 0
    myLastCol = 0
    Set dummyRng = .UsedRange
    On Error Resume Next
    myLastRow = _
      .Cells.Find("*", after:=.Cells(1), _
        LookIn:=xlFormulas, lookat:=xlWhole, _
        searchdirection:=xlPrevious, _
    myLastCol = _
      .Cells.Find("*", after:=.Cells(1), _
        LookIn:=xlFormulas, lookat:=xlWhole, _
        searchdirection:=xlPrevious, _
    On Error GoTo 0
    If myLastRow * myLastCol = 0 Then
        .Range(.Cells(myLastRow + 1, 1), _
          .Cells(.Rows.Count, 1)).EntireRow.Delete
        .Range(.Cells(1, myLastCol + 1), _
          .Cells(1, .Columns.Count)).EntireColumn.Delete
    End If
  End With
Next wks
End Sub


  1. Tried to run but it stopped at below point:

    .Range(.Cells(myLastRow + 1, 1), _

  2. Sound strange, I used Office 2010 don't know if there is an impact if you have a different version maybe.
    Try to access first the .Range to see where your error is.
    Cannot give you more advises....

  3. This comment has been removed by a blog administrator.

  4. How long did your 59M file take to run through the macro?

  5. It was a very long time ago i do not remember and i can't find the original file, sorry

  6. will it work if I have a lot of cells with zeros in them inwhich I need?

  7. It worth trying it, i think yes but not sure

  8. Positive site, where did u come up with the information on this posting?I have read a few of the articles on your website now, and I really like your style. Thanks a million and please keep up the effective work. compress jpeg to 100kb

  9. That code worked perfectly with me ... Thanx too too too much for you ...

  10. Hello It’s really a nice and helpful piece of info. I am happy that you shared this helpful info with us. Please stay us informed like this. Thank you for sharing. bye

  11. Thanks the code is working on my excel sheet.


Note: Only a member of this blog may post a comment.