Selecting data to plot

  • Thread starter Thread starter Simon
  • Start date Start date
S

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.
 
something like this might help

Sub hideneg()
For Each c In Selection
If c < 0 Then c.EntireRow.Hidden = True
Next
End Sub
 
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/
_______
 
Back
Top