Hi Rob,

Before you delete your rows/cols run this.

Dim oCht As ChartObject
For Each oCht In ActiveSheet.ChartObjects
oCht.Placement = xlFreeFloating
Next

You might want to temporarily store original "Placement" settings and
re-apply when done. Keep in mind when you delete rows/cols any objects
therein are not deleted, just a little lost!

Regards,
Peter T

"Rob" wrote in message
...
Hi,
The following code removes unwanted rows and columns, albeit it's also
removing formatting and charts that I want to keep in the worksheet.

Is there a way to retain the formatting and charts but also delete
unwanted
rows and columns that are making the file much too large?

Thanks, Rob

Sub DeleteUnused()
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