Dear Ed,
Many thanks. That is indeed what I ended up doing but, given the size of the
data, I would of course prefer not to have to do so. But many thanks for the
code (I didn't try writing one myself so this is very helpful).
Best wishes, Boris.

"Ed Ferrero" wrote:

Hi Boris,

Hi Ed,
Just to say your trick (sort of) works in Excel 2007 too. Only the graphs
are then limited to just 4000 datapoints per series (no good for me) and
the
charting engine also screws up the sapcing eg between the title , x-axis
title and labes, and the chart area. Also, you lose any secondary y axis
and
the whole workbook becomes incredibly slow but... in principle it works.
Best wishes, and many thanks, Boris.

More than 4000 data points - I did not test with a large data sample.

Ok you could try to copy the series range as values, then replace the #N/A
values with blanks, then chart that. Here is a little VBA code that will do
it for you - it copies the original data to the next column so that you do
not lose the original formulas.

Sub MakeChartable()
Dim rngInput As Range

Application.ScreenUpdating = False

' enter series input range here
' assume that series is in range D4:D4000
Set rngInput = Worksheets(1).Range("D4:D4000")

' copy the input range, paste values in next column
' and replace the #N/A values with blanks
rngInput.Copy

With rngInput.Offset(0, 1)
.PasteSpecial xlPasteValues
.Replace What:="#N/A", Replacement:=""
End With

Set rngInput = Nothing
Application.ScreenUpdating = True
End Sub

Ed Ferrero
www.edferrero.com