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

11 comments
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, _
        searchorder:=xlByRows).Row
    myLastCol = _
      .Cells.Find("*", after:=.Cells(1), _
        LookIn:=xlFormulas, lookat:=xlWhole, _
        searchdirection:=xlPrevious, _
        searchorder:=xlByColumns).Column
    On Error GoTo 0
    If myLastRow * myLastCol = 0 Then
        .Columns.Delete
    Else
        .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

11 comments :

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

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

    ReplyDelete
  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....

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

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

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

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

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

    ReplyDelete
  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

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

    ReplyDelete
  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

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

    ReplyDelete

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