In 9 Steps to Simpler Chart Formatting I suggested using data labels to identify each series rather than using a legend. I have a small VBA procedure that I use for this. It labels the last point of each series, and removes other labels. It also has an error trap that skips points that are not plotted because of blank cells or #N/A errors.

Sub LabelLastPoint()
  Dim mySrs As Series
  Dim iPts As Long
  Dim bLabeled As Boolean
  If ActiveChart Is Nothing Then
    MsgBox "Select a chart and try again.", vbExclamation
  Else
    For Each mySrs In ActiveChart.SeriesCollection
      bLabeled = False
      With mySrs
        For iPts = .Points.count To 1 Step -1
          If bLabeled Then
            ' handle error if point isn't plotted
            On Error Resume Next
            ' remove existing label if it's not the last point
            mySrs.Points(iPts).HasDataLabel = False
            On Error GoTo 0
          Else
            ' handle error if point isn't plotted
            On Error Resume Next
            ' add label
            mySrs.Points(iPts).ApplyDataLabels _
                ShowSeriesName:=True, _
                AutoText:=True, LegendKey:=False
            bLabeled = (Err.Number = 0)
            On Error GoTo 0
          End If
        Next
      End With
    Next
    ActiveChart.HasLegend = False
  End If
End Sub
 

To implement this procedure, follow the steps in How To: Use Someone Else’s Macro.

Possibly Related Posts:

• Connect Two XY Series with Arrows (VBA)
• Order of Points in XY and Line Charts
• VBA to Split Data Range into Multiple Chart Series
• Extract Chart Data
• Enhanced Export Chart Procedure
• Quick VBA Routine: XY Chart with Axis Titles
• Chart Event to Highlight a Series
• Chart Event Class Module to Highlight a Series