Excel Diet...Panacea or not


Board Regular
Feb 23, 2008
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?



[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 
                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 
                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 
                    If j > LastRow Then 
                        LastRow = j 
                    End If 
                    Do Until .Cells(j, k).Left > Shp.Left + Shp.Width 
                        k = k + 1 
                    If k > LastCol Then 
                        LastCol = k 
                    End If 
                End If 
            .Range(Cells(1, LastCol + 1).Address & ":IV65536").Delete 
            .Range(Cells(LastRow + 1, 1).Address & ":IV65536").Delete 
        End With 
    Application.ScreenUpdating = True 
    Application.DisplayAlerts = True 
End Sub 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
I can't see anything there that should fail in 2003. Which line is causing the error?
Upvote 0
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.
Upvote 0
think I may have been missing the "Option Explicit" when using it on the 2003, would this have caused the error?
Last edited:
Upvote 0
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!
Upvote 0
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

Upvote 0
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?
Upvote 0
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.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back