Excel Diet...Panacea or not

[somesoldiers]

Hi Guys, I discovered this Excel Diet http://www.vbaexpress.com/kb/getarticle.php?kb_id=83
searching elsewhere and was wondering how reliable it is? I have tried it on files of 35Mb which it has flattened to 300kb, it seems to have kept all the formulae that I wanted but I was just wondering whether anyone else had any experience good or bad? It seems quite the panacea for my purposes but is it too good to be true?

I also seem to have no problem running in 2007 but when I try in 2003 I am getting a syntax error and the macro won't run, I have changed nothing just am running from a colleagues pc which has not yet upgraded to 2003. Any ideas anyone?

thanks

Noel

[COLOR=black][FONT=Verdana]Option Explicit 

Sub ExcelDiet() 
     
    Dim j               As Long 
    Dim k               As Long 
    Dim LastRow         As Long 
    Dim LastCol         As Long 
    Dim ColFormula      As Range 
    Dim RowFormula      As Range 
    Dim ColValue        As Range 
    Dim RowValue        As Range 
    Dim Shp             As Shape 
    Dim ws              As Worksheet 
     
    Application.ScreenUpdating = False 
    Application.DisplayAlerts = False 
     
    On Error Resume Next 
     
    For Each ws In Worksheets 
        With ws 
             'Find the last used cell with a formula and value
             'Search by Columns and Rows
            On Error Resume Next 
            Set ColFormula = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, _ 
            LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious) 
            Set ColValue = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _ 
            LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious) 
            Set RowFormula = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, _ 
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious) 
            Set RowValue = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _ 
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious) 
            On Error Goto 0 
             
             'Determine the last column
            If ColFormula Is Nothing Then 
                LastCol = 0 
            Else 
                LastCol = ColFormula.Column 
            End If 
            If Not ColValue Is Nothing Then 
                LastCol = Application.WorksheetFunction.Max(LastCol, ColValue.Column) 
            End If 
             
             'Determine the last row
            If RowFormula Is Nothing Then 
                LastRow = 0 
            Else 
                LastRow = RowFormula.Row 
            End If 
            If Not RowValue Is Nothing Then 
                LastRow = Application.WorksheetFunction.Max(LastRow, RowValue.Row) 
            End If 
             
             'Determine if any shapes are beyond the last row and last column
            For Each Shp In .Shapes 
                j = 0 
                k = 0 
                On Error Resume Next 
                j = Shp.TopLeftCell.Row 
                k = Shp.TopLeftCell.Column 
                On Error Goto 0 
                If j > 0 And k > 0 Then 
                    Do Until .Cells(j, k).Top > Shp.Top + Shp.Height 
                        j = j + 1 
                    Loop 
                    If j > LastRow Then 
                        LastRow = j 
                    End If 
                    Do Until .Cells(j, k).Left > Shp.Left + Shp.Width 
                        k = k + 1 
                    Loop 
                    If k > LastCol Then 
                        LastCol = k 
                    End If 
                End If 
            Next 
             
            .Range(Cells(1, LastCol + 1).Address & ":IV65536").Delete 
            .Range(Cells(LastRow + 1, 1).Address & ":IV65536").Delete 
        End With 
    Next 
     
    Application.ScreenUpdating = True 
    Application.DisplayAlerts = True 
     
End Sub 
[/FONT][/COLOR]

 

[RoryA]

I can't see anything there that should fail in 2003. Which line is causing the error?

 

[somesoldiers]

Hi Rory

just retried it there in Excel 2003 and it ran through no problem. It seems to be working ok but is it too good to be true? would be using it in work and would rather steer well clear if there's any problems.

 

[somesoldiers]

think I may have been missing the "Option Explicit" when using it on the 2003, would this have caused the error?

 

[RoryA]

Nope, that just causes you to declare all variables.
You shouldn't have any issues with that code except that it is designed for 2003 and earlier - note the use of IV65536 as last available cell!

 

[somesoldiers]

Oh I see and the 2007 is designed to have more rows than that so presumably if I had data in a cell outside that range and ran it in 2007 it would only work that far, not much chance of that thankfully.

Thanks a lot for you help

Noel

 

[mdfreeman]

Hey - It seems the ExcelDiet macro wiped some of the data from my sheet. Does anyone has a copy that works better with Excel 2007's extra rows?

 

[somesoldiers]

try the official microsoft add-in

http://www.microsoft.com/downloads/...6C-B873-48CC-B842-DA999C848C82&displaylang=en

 

[energman58]

But the official MS add-in is also only for Excel up to 2003 - I guess the same issues are likely to come up for 2007 and 2010 regarding size so why not change the VBA code for the unofficial one - I cant see anything there that would cause a problem for 2007.