Selecting data to plot

[Simon]

Hi,

I am having trouble with an excel chart.
I have a data range that I want to plot, but I only want
to plot where the formula in each cell of the data range
returns a positive value.
I also don't want to keep changing the data range
manually.
I know you can choose not to plot empty cells in a range,
but even if I specify that the cells are "" in the
formula, it still plots them as the cells are not
actually empty (i.e. they still have the formula.

Any help much appreciated.

 

[Don Guillett]

something like this might help

Sub hideneg()
For Each c In Selection
If c < 0 Then c.EntireRow.Hidden = True
Next
End Sub

 

[Jon Peltier]

Don shows one way. You could also set up an autofilter with a custom
condition (___ does not equal ___, keeping the second dropdown empty).

You also can change your formula to return NA() instead of "". "" looks
blank to you and me, but to Excel it's a text string, and it will always
evaluate numerically to zero. NA() returns the ugly #N/A error in the
sheet, but it doesn't plot in most types of chart.

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