I have a scatter chart based on data in another tab. I wrote a
MouseDown routine which displays the name of the item when you click
on it. I use GetChartElement to return the PointIndex of the entry.

If I use AutoFilter to reduce the list size, the chart only displays
the filtered list (less dots). This is great, but the PointIndex
returned is then based on the *filtered* list, not the whole list, and
I can't figure out how to get the value in the Nth cell down from the
top, counting only the *filtered* (i.e. visible) items.

I have been using

NewText = Sheets("MySheet").Range("D1").Offset(arg2)

where arg2 comes from the GetChartElement function. But the Offset
function only considers the whole list, not the filtered list. Can
anyone give me the syntax that will work even if the list has been
filtered?

Thanks so much for your help!

-- Charlie

Couldn't you just look at each cell and test to see if the rowheight was 0. If
it was, then skip that one and keep going:

Option Explicit
Sub testme()
Dim arg2 As Long
Dim VisibleCtr As Long
Dim myCell As Range

arg2 = 3 'for example
VisibleCtr = 0

For Each myCell In ActiveSheet.AutoFilter.Range.Columns(1).Cells
If myCell.EntireRow.Hidden Then
'do nothing
Else
VisibleCtr = VisibleCtr + 1
End If
If VisibleCtr = arg2 Then
'go it!
'do what you want
MsgBox myCell.Row & "--" & VisibleCtr
'and
Exit For
End If
Next myCell

End Sub

Add some checking so you don't exceed your filtered data.


Charlie wrote:

I have a scatter chart based on data in another tab. I wrote a
MouseDown routine which displays the name of the item when you click
on it. I use GetChartElement to return the PointIndex of the entry.

If I use AutoFilter to reduce the list size, the chart only displays
the filtered list (less dots). This is great, but the PointIndex
returned is then based on the *filtered* list, not the whole list, and
I can't figure out how to get the value in the Nth cell down from the
top, counting only the *filtered* (i.e. visible) items.

I have been using

NewText = Sheets("MySheet").Range("D1").Offset(arg2)

where arg2 comes from the GetChartElement function. But the Offset
function only considers the whole list, not the filtered list. Can
anyone give me the syntax that will work even if the list has been
filtered?

Thanks so much for your help!

-- Charlie

--

Dave Peterson

Dave,

Thanks so much! That idea worked!

All the best,

-- Charlie


Dave Peterson wrote in message ...
Couldn't you just look at each cell and test to see if the rowheight was 0. If
it was, then skip that one and keep going:

Option Explicit
Sub testme()
Dim arg2 As Long
Dim VisibleCtr As Long
Dim myCell As Range

arg2 = 3 'for example
VisibleCtr = 0

For Each myCell In ActiveSheet.AutoFilter.Range.Columns(1).Cells
If myCell.EntireRow.Hidden Then
'do nothing
Else
VisibleCtr = VisibleCtr + 1
End If
If VisibleCtr = arg2 Then
'go it!
'do what you want
MsgBox myCell.Row & "--" & VisibleCtr
'and
Exit For
End If
Next myCell

End Sub

Add some checking so you don't exceed your filtered data.


Charlie wrote:

I have a scatter chart based on data in another tab. I wrote a
MouseDown routine which displays the name of the item when you click
on it. I use GetChartElement to return the PointIndex of the entry.

If I use AutoFilter to reduce the list size, the chart only displays
the filtered list (less dots). This is great, but the PointIndex
returned is then based on the *filtered* list, not the whole list, and
I can't figure out how to get the value in the Nth cell down from the
top, counting only the *filtered* (i.e. visible) items.

I have been using

NewText = Sheets("MySheet").Range("D1").Offset(arg2)

where arg2 comes from the GetChartElement function. But the Offset
function only considers the whole list, not the filtered list. Can
anyone give me the syntax that will work even if the list has been
filtered?

Thanks so much for your help!

-- Charlie