I wasn't aware in my earlier posts the OP was trying to delete cells with
chart source data. If he is that's a neat suggestion. Only thing to be aware
of is if the formula string approaches 1024 characters (eg many points with
big decimals + long XValues).

What I do is convert to named arrays, the only limit being 5461 points per
series in xl97 & xl2000 (afaik). But quite a lot of code.

Regards,
Peter T

"R.VENKATARAMAN" &&& wrote in message
...
there is another method without using vba
click the chart
you will get in the formula bar
=sereis(.........(in cell addresses...)
highlight this formula including the sign <=
hit function key F9
the formula bar changes to (=seriess.......actual dta..........)
hit <enter
now you can remove the data from the sheet.
chart is based on actual data and not cell values.

try that.


Rob wrote in message
...
Thanks David, always useful to see how others have tackled issues.

Regards, Rob
"David" wrote in message
...
Hi Rob,
I was trying to accomplish something similar to what you are trying to
do,
only I wanted to delete all of the source data. I could not find the
post
or
that helped me out directly, but the following code creates a picture
of
your
chart. It will not change the appearance of the chart. It worked great
for
me. Hope it will help you.
Dim chartShp As Shape
Dim exLeft As Single, exTop As Single
Set chartShp = ActiveSheet.Shapes(ChartObjectName)
exLeft = chartShp.Left
exTop = chartShp.Top
chartShp.CopyPicture xlScreen
chartShp.Delete
ActiveSheet.Paste
Selection.Left = exLeft
Selection.Top = exTop

"Rob" wrote:

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