Data Series Formatting with a Dynamic Range

To do this neatly, you do need to use a dynamic range so that you can easily determine the last point actually populated.
In the attached (Data Series Formatting with a Dynamic Range.xlsm):

1. See the named calculated range 'TestSeries' and see how it adjusts in size according to the number of entries
2. Also see how the dynamic named range is used instead of a normal range reference in chart data setup.
3. The last point of the series has been set as MarkerStyle = circle.
4. Now enter a new value in the chart data, you'll see the line extend, BUT your point is illustrated - the marker is now on the 2nd last point.
5. Now click the button, and the formatting is restored.

The work for refreshing the series formatting is done by this proc:

Public Sub RefreshSeriesTerminator(SheetName As String, ChartName As String)
    Dim Ser As Excel.Series
    Dim Pnt As Excel.Point
    Dim p As Long
    For Each Ser In ThisWorkbook.Worksheets(SheetName).ChartObjects(ChartName).Chart.SeriesCollection
        For p = 1 To Ser.Points.Count
            Set Pnt = Ser.Points(p)
            If p = Ser.Points.Count Then
                Pnt.MarkerStyle = xlMarkerStyleCircle
            Else
                Pnt.MarkerStyle = xlMarkerStyleNone
            End If
        Next p
    Next
End Sub

Aargh. Ignore the extra proc 'Private Sub SetChartData' in the module; I didn't clean up before posting.