1

This macro displays text labels from the source worksheet when double-clicking a dot on a scatterplot chart. The chart is updated when the source worksheet is filtered on any of several columns. The macro is supposed to detect this filtering, and update the label values accordingly.

This works when the data are sorted on the filtered column, but not when it is also sorted on some other column on which the data are not sorted, which causes additional hidden rows.

The problem is that despite the fact that some rows are hidden, the macro counts both visible AND hidden rows, starting from the first visible row (as if xlCellTypeVisibleis not working).

To clarify: As long as there are no hidden rows in the filtered subset, the labels display correctly starting with the first visible row on the filtered subset. However when an additional filter is applied on a column that is not sorted by the filtered value, the labeling gets screwed up due to counting of the interspersed hidden rows in addition to the visible rows.

Details: - The starting row is calculated correctly to whatever the first visible row is. - The Arg2 value is also correctly set to the appropriate visible row in the series, and the xData and yData values on the label are correct even when filtered on non-sorted rows! (So Arg2 does skip any hidden rows, consistent with the series that is displayed on the chart.)

But the labels from other columns that are incorrect.

Basically, I need to fetch my label text from row number Arg2 from the range of visible rows on the filtered sheet.

I am guessing the problem is in sid = .cells section where the count uses all rows instead of just visible rows. Again, the actual counts (when displayed with Msgbox) do point to the correct visible row if I visually count down the rows on the source worksheet. But the actual text in the label is based on applying this count to both hidden and visible rows, thus it comes up with a wrong row that is higher up in the data.

I have tried changing sid = .cells to sid = r.cells but no luck, in fact it starts counting from the very first row in the data rather than the first visible row. It looks like the SpecialCells(xlCellTypeVisible) is only working as expected when identifying the first visible row, but gets confused on any subsequent hidden rows.

Any help would be appreciated. I am new to VBA so please be clear/specific!

Public WithEvents myChartClass As Chart

Private Sub myChartClass_BeforeDoubleClick(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)
Dim ser As Series
Dim pt As Point
Dim xData As Double, yData As Double
Dim sid As String

'declare vars used for calculating row number for filtered data
Dim r As Range
Dim StartRow As Long

Cancel = True
For Each ser In Me.SeriesCollection
    ser.HasDataLabels = False
Next

If ElementID = xlSeries Then
    If Arg2 > 0 Then
        With Worksheets("MySheetName")
            Set ser = Me.SeriesCollection(Arg1)
             xData = ser.XValues(Arg2)
             yData = ser.Values(Arg2)
            Set pt = ser.Points(Arg2)

'calculate starting row when table is filtered on any variable
Set r = Worksheets("MySheetName").Range("A:A").Rows.SpecialCells(xlCellTypeVisible)
StartRow = r.Row - 1 'starting row is the first visible row minus the table header

            'grab label from the row associated with the clicked point on chart
            'the case number signifies the series of the chart in the order visible in Select Data chart properties window
            Select Case Arg1
            Case 1  'series 1
                sid = .Cells(Arg2 + StartRow, "D") & vbLf & "label1: " & .Cells(Arg2 + StartRow, "C") & vbLf & "label2: " & .Cells(Arg2 + StartRow, "L") & vbLf & "label3: " & .Cells(Arg2 + StartRow, "U")
            Case 2  'series 2
                sid = .Cells(Arg2 + StartRow, "D") & vbLf & "label1: " & .Cells(Arg2 + StartRow, "C") & vbLf & "label2: " & .Cells(Arg2 + StartRow, "L") & vbLf & "label3: " & .Cells(Arg2 + StartRow, "U")
            End Select

            pt.HasDataLabel = True
            pt.DataLabel.Characters.Font.Size = 11
            pt.DataLabel.Characters.Font.Bold = True
            pt.DataLabel.Text = sid & vbLf & "(" & xData & " , " & yData & ")"

    'MsgBox "r: " & r.Count
    'MsgBox "StartRow: " & StartRow
    'MsgBox "Arg1: " & Arg1
    'MsgBox "Arg2: " & Arg2

        End With
    End If
End If
End Sub

2 Answers 2

1

Your diagnosis is correct -- VBA ignores hidden/unhidden status when parsing cell references with .Cells. A brute-force cell-counting method is the only thing I've found to work:

Dim iter As Long, findCount As Long, workCel as Range

' This is okay as long as you are guaranteed only to have one header row.
Set workCel = Worksheets("MySheetName").Cells(2, 1)

' No cells found yet
findCount = 0

' Start iterator at zero
iter = 0

Do  
    ' Check row for hidden status
    If Not workCel.Offset(iter, 0).EntireRow.Hidden Then
        ' Row is visible; increment number of visible rows found
        findCount = findCount + 1
    End If

    ' Increment iterator
    iter = iter + 1

' Stop looping once the number of found rows reaches the desired count
Loop Until findCount >= Arg2

The needed index should fall out of the above code as the value of iter:

sid = .Cells(iter + StartRow, "D") & vbLf & ...

No post-decrement is necessary, because .Offset(n, 0) refers to the n+1th row of a range starting on a given cell.

To note, the .SpecialCells(xlCellTypeVisible) function is probably working correctly. The problem is, since the Range is 'interrupted' by the various hidden rows, it consists of multiple Areas (see here: http://msdn.microsoft.com/en-us/library/office/ff196243(v=office.15).aspx). This completely blows up normal .Cells(...)-type indexing. It's starting from the first row of data because your header row is unhidden & thus anchors your r range.

1

@Brian, Thanks again -- your answer and reference to Areas led me to look into ways to obtain the number of the visible row which corresponds to the Arg2 value in the series.

Did some more research and found a solution that seems similar in principle, but counts rows rather than cells (http://www.ozgrid.com/forum/showthread.php?t=23611). This seems to work:

Public WithEvents myChartClass As Chart

Private Sub myChartClass_BeforeDoubleClick(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)
Dim ser As Series
Dim pt As Point
Dim xData As Double, yData As Double
Dim sid As String

'declare vars used for calculating row number for filtered data
Dim rng As Range, rngArea As Range, lRows As Long, lRow2 As Long

Cancel = True
For Each ser In Me.SeriesCollection
    ser.HasDataLabels = False
Next

If ElementID = xlSeries Then
    If Arg2 > 0 Then
        With Worksheets("MySheetName")
            Set ser = Me.SeriesCollection(Arg1)
             xData = ser.XValues(Arg2)
             yData = ser.Values(Arg2)
            Set pt = ser.Points(Arg2)

'check autofilter;
    If Not .AutoFilterMode Then
        MsgBox "Please enable autofilter on source worksheet."
        Exit Sub
    End If

'set a range = to visible cells (excluding the header)
Set rng = .AutoFilter.Range.Offset(1, 0).Resize(.AutoFilter.Range.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)

'calculate starting row when table is filtered on any variable;
'loop through areas until row is found;
    lRows = 0
    For Each rngArea In rng.Areas
        lRows = lRows + rngArea.Rows.Count
        If lRows >= Arg2 Then
            lRow2 = rngArea.Item(Arg2 - (lRows - rngArea.Rows.Count)).Row
            Exit For
        End If
    Next rngArea

            'grab label from the row associated with the clicked point on chart
            'the case number signifies the series of the chart in the order visible in Select Data chart properties window
            Select Case Arg1
            Case 1  'series 1
            sid = .Cells(lRow2, "D") & vbLf & "label1: " & .Cells(lRow2, "C") & vbLf & "label2: " & .Cells(lRow2, "L") & vbLf & "label3: " & .Cells(lRow2, "U")
            Case 2  'series 2
            sid = .Cells(lRow2, "D") & vbLf & "label1: " & .Cells(lRow2, "C") & vbLf & "label2: " & .Cells(lRow2, "L") & vbLf & "label3: " & .Cells(lRow2, "U")
            End Select

            pt.HasDataLabel = True
            pt.DataLabel.Characters.Font.Size = 11
            pt.DataLabel.Characters.Font.Bold = True
            pt.DataLabel.Text = sid & vbLf & "(" & xData & " , " & yData & ")"


    'MsgBox "lRow2: " & lRow2

        End With
    End If
End If
End Sub
3
  • Yep, this should also work. I'd thought in general terms of going about it by digging into the Areas, but the additional bookkeeping burden of having to count up the number of rows of the various Areas seemed like more cost than benefit. In this case it's just a coding style preference, though, since scanning down the Range is known-functional.
    – hBy2Py
    Jan 2, 2015 at 17:14
  • Ok. Thank you again for your help! (also if the Q was useful would you consider ^)
    – A.S
    Jan 2, 2015 at 17:22
  • Sure thing, ping me any time.
    – hBy2Py
    Jan 2, 2015 at 17:27

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .