impius
10-21-2008, 09:32 AM
Hello all...
I found the following code that reduces workbook size by deleting unused cells. It works great and can drastically reduce workbook size. The only problem I am haveing with it is that if there is a chart or picture and no words below the chart it deletes the chart. Can anyone help modify this code to eliminate that problem?
Thanks!
Dim X As Integer
Dim LastRow As Long
Dim LastCol As Integer
Application.ScreenUpdating = False
On Error Resume Next
For X = 1 To Sheets.Count
Sheets(X).Activate
LastRow = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _
lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastCol = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _
lookat:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Range(Cells(1, LastCol + 1), Cells(65536, 256)).Delete
Range(Cells(LastRow + 1, 1), Cells(65536, 256)).Delete
Next X
On Error GoTo 0
Application.ScreenUpdating = True
End If
I found the following code that reduces workbook size by deleting unused cells. It works great and can drastically reduce workbook size. The only problem I am haveing with it is that if there is a chart or picture and no words below the chart it deletes the chart. Can anyone help modify this code to eliminate that problem?
Thanks!
Dim X As Integer
Dim LastRow As Long
Dim LastCol As Integer
Application.ScreenUpdating = False
On Error Resume Next
For X = 1 To Sheets.Count
Sheets(X).Activate
LastRow = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _
lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastCol = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _
lookat:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Range(Cells(1, LastCol + 1), Cells(65536, 256)).Delete
Range(Cells(LastRow + 1, 1), Cells(65536, 256)).Delete
Next X
On Error GoTo 0
Application.ScreenUpdating = True
End If