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

9 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

9 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