#1
|
|||
|
|||
Method to remove overlap in data labels for single series
Hi guys,
I've created a fairly simple algorithm to remove overlapping data labels on a single series in Excel. It's based on this Stack Overflow page, and it's quite effective in dealing with graphs with sometimes close-spaced single series. I'm posting it in hopes that some will find it useful and that others might have suggestions for making it more efficient. First: I use a loop to detect which datapoints have labels, and then store the points in an array and the number of points as long: Code:
Sub DataArray() Dim dLabels() As DataLabel Dim i As Long Dim k As Long ReDim dLabels(1 To 1868) 'my range Set FTIRLine = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1) k = 1 For i = 1 To FTIRLine.Points.Count If FTIRLine.Points(i).HasDataLabel = _ True Then 'tests to see if point has datalabel Set dLabels(k) = FTIRLine.Points(i).DataLabel k = k + 1 End If Next Call AdjustLabels(dLabels, k) 'sends labels, number of labels, to function End sub Code:
Sub AdjustLabels(ByRef v() As DataLabel, ByVal m As Long) Dim o As Long hight = 8.5 Wdth = 35 o = 1 Do If v(o).Left < (v(o + 1).Left + Wdth) And v(o).Top < (v(o + 1).Top + hight) And v(o).Top > (v(o + 1).Top - hight) Then v(o).Top = v(o).Top + hight / 2 v(o + 1).Top = v(o).Top - hight / 2 'v(o).Left = v(o).Left + Wdth / 2 'v(o + 1).Left = v(o).Left - Wdth / 2 Else End If o = o + 1 Loop Until o = m - 1 End sub Code:
Sub DataArrayCaller() Dim q As Long q = 1 Do DataArray q = q + 1 Loop Until q = 5 End Sub |
Tags |
charts, datalabels, overlap |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Method or data member not found | gbaker | Excel Programming | 5 | 07-06-2012 05:20 AM |
Remove Portion of Field Labels | sbianco | Word | 5 | 03-07-2012 04:52 AM |
Remove header/footers from a single page | qwerty2k12 | Word | 1 | 03-01-2012 08:14 PM |
Powerpoint: adding data to trend lines w/o data labels | HaiLe | PowerPoint | 0 | 04-11-2011 09:21 AM |
Data Series | Zach | Excel | 1 | 11-05-2010 02:45 PM |