Defining a range by the contents of cells?

[travis]

I have many rows of data and I would like to put some of that data into
a range (so I can chart that range) conditional on the contents of the
cells.

If the cells are a bunch of zeros, I do NOT want them to be included in
the range.

However if they have non-zero values in there, even if it is just one
or two non-zero values in a row with mostly zeros, I'd like them to be
in the range.

Can anyone help?

Travis

 

[Guest]

You can make a range with the UNION() function:

Sub gsnu()
Dim r As Range
Dim rNotEmpty As Range

For Each r In Selection
z = r.Value
If z = 0 Then
Else
If rNotEmpty Is Nothing Then
Set rNotEmpty = r
Else
Set rNotEmpty = Union(rNotEmpty, r)
End If
End If
Next

MsgBox (rNotEmpty.Address)
End Sub



Delete the msgbox once you are satisfied.