Identify last datapoints in PivotChart

[paradox715]

I am running a PivotChart (a line chart) and I want to identify the last non-blank datapoint for each series. The problem is that in a PivotChart, each series has the same number of datapoints (i.e. 10) even though each of my series in reality is different in size ... it's just that the PivotTable has blanks for the last data points when not in use. To keep this simple in my example code below, the part I'm really just having trouble with is getting the SeriesCollection(1) passed into an array, so that I can check how many non-blanks there are... any tips?

Sub FinalPointMarker()
Dim myArray As Variant
Dim ValidCounter As Integer
Dim x as variant

Set x = ActiveChart.SeriesCollection(1)

'Having trouble generating an array from a series collection here ...
myArray() = x

'The following works ok if I had instead setup a simple array manually in the prior step ...
For i = LBound(myArray) To UBound(myArray)
If (myArray(i) <> "") Then
    ValidCounter = ValidCounter + 1
End If

Next i

MsgBox ValidCounter

End Sub

 

[Domenic]

You can assign the values from your series like this...

myArray = ActiveChart.SeriesCollection(1).Values

Hope this helps!