Adding ranges

[Bryan via OfficeKB.com]

Hello,
I am creating a pie chart and I only want to plot the ranges that
correspond to values greater than 0. So I'd like to loop over the possible
entries and set my chart range to correspong to only those that are non-
zero. I'm assuming that I'll need to add ranges somehow to do this, but I
can't seem to get it right. How do you add ranges so that they can be used
for charts?

Here is an example of what I mean:


| A | B
_________________________
1 | name1 | 5
2 | name2 | 0
3 | name3 | 3
4 | name4 | 7



So I'd want the range to be "A1:B1,A3:B4" for my pie chart. I'd like to
loop over rows 1 through 4, and add the ranges corresponding to the non-
zero entries in column B.

Here is the loop that I created:

For i = 3 To 10
If Worksheets("Program").Cells(i, 12) > 0 Then
Set PieChartRange = PieChartRange + Worksheets("Program").Range
(Cells(i, 11), Cells(i, 12))
End If
Next i


But the statement 'Set PieChartRange = ...' doesn't work. I'd like to use
that variable in the following block:


With ActiveSheet.ChartObjects.Add _
(Left:=440, Width:=330, Top:=495, Height:=220)
With .Chart
.SetSourceData Source:=ActiveSheet.Range(PieChartRange.Address)
'....
'....


I'd greatly appreciate any help.

 

[Tom Ogilvy]

Can you hide the rows with zero. If you can, there is an option to no plot
hidden rows.

If you can't do that, then you will need to set up a second contiguous area
where you build your data with no zero cell data and use that as the source
for your chart.

 

[Bryan via OfficeKB.com]

Actually, I just got help from someone else on how to do this:

-----------
Dim myChartRange As Range
With Selection
For k = 1 To .Rows.Count
If .Cells(k, 2) > 0 Then
If myChartRange Is Nothing Then
Set myChartRange = .Rows(k)
Else
Set myChartRange = Union(myChartRange, .Rows(k))
End If
End If
Next
End With

 

[Tom Ogilvy]

Yes, that will work for a single series category style chart and I guess
that is what you have. Glad you have a solution.