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, _
        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

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

CD
marinir
bantal-silikon15.blogspot.com
bumbu-pecel01.blogspot.com/
seo-guru01.blogspot.com/
seo-konsultasi-mudah.blogspot.com/l
marinir-seo-konsultan-12a.blogspot.com/
http://dfes12.blogspot.com/
agen-seo19.blogspot.com/
agen-seo20.blogspot.com/
agen-seo16.blogspot.com/
agen-seo18.blogspot.com/
jksbindo.blogspot.com/
jkt-49.blogspot.com/
seokonsultan1.blogspot.com
guru-seo-malang.blogspot.com/
seo-aaa.blogspot.com/
marinir-seo-8.blogspot.com/2015/05/rty.html
vvtiin.blogspot.com/2015/05/rqw.html
agen-seo17.blogspot.com/2015/05/rtq.html
asdf-14.blogspot.com/2015/05/qwr.html
bantaljkt1.blogspot.com/2015/05/qwa.html
larismanisyaluk.blogspot.com/2015/05/q.html
bantalku1.blogspot.com/2015/05/b.html
bantal999.blogspot.com/2015/05/r.html

distrobantal.blogspot.com/
bantalsilikon01.blogspot.com/
sabunherbalya.blogspot.com/
kesethandukmalang.blogspot.com/
bantalmalangmurah.blogspot.com/
restoran-seo.blogspot.com/
distro-seo.blogspot.com/
jualsangkarpleci.blogspot.com/
marinir-seo.blogspot.com/
kopiluwakliar01.blogspot.com/
bumbu-pecel-malang.blogspot.com/

A Dart Rider said...

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

Post a Comment