hi!
how to exclude empty cell with formula from a chart?i have a table of
26R*50C that contains formula.the formula will returns #N/A for cells with no
data.currently the cells with data is 18R*16C and these size will always
change.
then i create a chart from the data that will change due to its data via
this macro:

Sub updateChart()
Sheets("Sheet 1").ChartObjects(1).Chart.SetSourceData _
Source:=Sheets("Sheet 2").Range("B2").CurrentRegion, PlotBy:=xlColumns
End sub

the macro runs perfectly with cells without formula but when it comes to
cells with formula,it will return the zero values to chart.
what am i doing right now is copy valuable cells to another cells and create
& update the chart from there.but its running very slow since i need to use
macro to copy it.
Sub MyMacro1()

a = 1
b = 1
y = 53

Do While Cells(a, b).Value < ""
x = a
Do While Cells(x, b).Value < ""
Cells(x, y).Value = Cells(x, b).Value
x = x + 1
Loop
b = b + 1
y = y + 1
Loop

End Sub

is there any other ways to solve this?thanks!
--
Regards,
Linda