hey

i have a table of data see image below of names of companies and then a value either +ve or -ve
I want to plot only the positive values in a pie chart. I don't want to reorder the data (unless at the end of the macro it will reorder it back again without changing the chart).

Screen Shot 2013-05-24 at 02.55.34.jpg

i have tried and tried and tried for hours and hours and hours to get this right. i am new to VB but keen to learn.

i hit on the idea of using a loop through the cells, and using the UNION function it will add to the range if it finds a positive value. Unfortunately I have had problems:
1. the top value isn't necessarily positive.
2. i want it to skip the pie chart (and complete the rest of my macro which does other stuff) if there are no positive values

the below code is fairly concise (apart from right at the end when formatting the charts which was recorded) so please please can you help and not be deterred by the length!

i would be very grateful, youve no idea how many hours ive spent trying.

thank you

note mkt is defined previously as the upper left cell in the table

Sub FinalPivot()
'
'
Charts

Dim aggcell As Range
Set aggcell = mkt.Offset(1, 1)
Dim aggend As Range
Set aggend = aggcell.End(xlDown).Offset(-1, 0)
Dim chartseries As Range
    
For i = aggcell.Row To aggend.Offset(1, 0).Row
            If Cells(i, aggcell.Column).Value >= 0 Then
            Set chartseries = Range(Cells(i, aggcell.Column - 1), Cells(i, aggcell.Column))
            GoTo Quit
            Else
            End If
Next i

For i = aggcell.Row To aggend.Row
    If Cells(i, aggcell.Column).Value >= 0 Then Set chartseries = Union(chartseries, Range(Cells(i, aggcell - 1), Cells(i, aggcell)))
Next i

Quit:

If chartseries Is Nothing Then GoTo Jump Else

            With ActiveSheet.ChartObjects.Add _
                    (Left:=100, Width:=375, Top:=75, Height:=225)
                With .chart
                    .ChartType = xlPie
                    .SetSourceData Source:=chartseries
                    .Parent.Name = "Pie"
                End With
            End With
                ActiveSheet.ChartObjects("Pie").Activate
                ActiveChart.ClearToMatchStyle
                ActiveChart.ChartStyle = 42
                ActiveChart.ClearToMatchStyle
                ActiveChart.Legend.Delete
                ActiveChart.SetElement (130)
                ActiveChart.Name = "Pie"
                ActiveSheet.ChartObjects("Pie").Activate
                ActiveSheet.Shapes("Pie").IncrementLeft -427
                ActiveSheet.Shapes("Pie").IncrementTop 101
                ActiveWindow.SmallScroll Down:=11
                ActiveSheet.ChartObjects("Pie").Activate
                ActiveSheet.Shapes("Pie").ScaleWidth 1.5069444444, msoFalse, _
                    msoScaleFromTopLeft
                ActiveSheet.Shapes("Pie").ScaleHeight 1.5069444444, msoFalse, _
                    msoScaleFromTopLeft
                ActiveWindow.SmallScroll Down:=-17
                ActiveSheet.ChartObjects("Pie").Activate
                ActiveSheet.Shapes("Pie").IncrementTop -30
                Range("Table1[#All]").Select

Jump: