Mac – Chart macro displaying incorrect labels from non-sequential visible rows on filtered worksheet

chartsmacrosmicrosoft excelmicrosoft-excel-2010vba

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

Best Answer

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.

Related Question