Rob -

If it's the formatting that is bloating the file, you don't want to retain it.

If the cells contain unseen characters, you can clear the cell contents without
affecting the formats (or deleting the cells) if you use .ClearContents instead of
..Delete in your code.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Rob wrote:

Tom,

The formatting covers a range something like A1:F130 whereas if I press End
and Home keys the cursor moves to cell X7368. The file originates from an
external source and seems to generate a vast range that increases the file
size 10 fold.

If it were a single sheet file I'd do manually but the file is often 10 or
more sheets with various data range on each.

Thanks, Rob


"Tom Ogilvy" wrote in message
...

If you delete the column or the row, then the formatting for that column
and/or row is also deleted. In fact, the formatting may be what is
causing
you to need to run this routine in the first place. so you may be chasing
your tail if you delete the rows and or columns and then replace the
formatting that was deleted.

--
Regards,
Tom Ogilvy


"Rob" wrote in message
...

Thanks Peter, need to experiment with where I place this but get the

general

idea of what it does. Need to figure out similar for formatting.
Thanks again, Robert

"Peter T" <peter_t@discussions wrote in message
. ..

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