Excel – Prevent Excel Chart Data Labels overlapping

You could use this addon http://www.appspro.com/Utilities/ChartLabeler.htm

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+1^th 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.