Friday, May 27, 2011

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

7 commentaires:

Anonymous said...

Tried to run but it stopped at below point:

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

Jeremy Cottino said...

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

kopiluwak nya said...
This comment has been removed by a blog administrator.
A Dart Rider said...

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

Jeremy Cottino said...

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

Unknown said...

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

Jeremy Cottino said...

It worth trying it, i think yes but not sure

Post a Comment