Determine Point in Filtered List for Chart

[John Michl]

I've run into a small problem. I have a XY chart that when activated,
runs some code that applies custom labels to each point. It works fine
UNTIL I filter the data in the source sheet. The chart shows the
correct number and location of the points but the labels are incorrect.


I apply the labels with code similar to the following:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
rngLabels = Range("A1:A10")
pts = ActiveChart.SeriesCollection(1).Points

For i = 1 to pts.Count
pts(i).ApplyDataLabels Type:=xlShowValue
pts(i).DataLabel.Text = rngLabels(i,1)
Next i
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
This worked fine when the list was unfiltered and the first cell in
rngLabels corresponded to the first point in the chart. But when the
list is filtered, there might only be 5 points in the chart and they
may not correspond to the first five points in the original list.

So...is there a better way for me to create the contents of the array
rngLabels that only includes those cells that are visible and will end
up in the chart?

Thanks

- John

 

[John Michl]

I figured out a fairly simple solution but I'm still open to other
suggestions. Here's what I came up with.

I created a new array equal in size to the number of points in the
series. I then checked each cell in the original list to determine if
it was filtered out or not. The value of the visible cells was added
to the new array which then was applied to the chart points.
dim arrLabels as variant
rngLabels = Range("A1:A10")
pts = ActiveChart.SeriesCollection(1).Points

ReDim arrLabels(1 To pts.Count)
i = 1

For Each cell In Range("ChartLabels_list")
If cell.EntireRow.Hidden = False Then
arrLabels(i) = cell.Value
i = i + 1
End If
Next cell


For i = 1 to pts.Count
pts(i).ApplyDataLabels Type:=xlShowValue
pts(i).DataLabel.Text = rngLabels(i,1)
Next i