Using VBA to Update X-Values

[LucasLondon]

Dear all,

Wondering if anyone can help me out with a little code I need. I created the macro below to update a chart with the latest data and this runs fine. However, despite several attempts, I can't seem to get it to update the X-axis value labels as well.

Sub Update_Chart()
ActiveSheet.ChartObjects(1).Activate
Set startseries = Range("e770").End(xlUp).Offset(-50, 0)
Set endseries = Range("e770").End(xlUp)
ActiveChart.SetSourceData Source:=Sheets("table").Range(startseries, endseries), PlotBy:= _
xlColumns
End Sub

For instance, assume that my data runs from E5:E300 and then the data increases to E310. I run the macro and it updates the actual series but the x axis labels still only reads from E5:E300. I then manually need to update the X axis values (effectively a date feild). Is there anyway code I can add to get this to update as well?

I tried adding the following to the code but it does not work how I want:

Sub Update_Chart2()
ActiveSheet.ChartObjects(1).Activate
Set startseries = Range("e770").End(xlUp).Offset(-50, 0)
Set endseries = Range("e770").End(xlUp)
Set startlabel = startseries.Offset(0, -4)
Set endlabel = endseries.Offset(0, -4)
ActiveChart.SetSourceData Source:=Sheets("table").Range(startlabel & ":" & endlabel, startseries & ":" & endseries), PlotBy:= _
xlColumns
End Sub

It returns the message: "The maximum number of data points you can use in a data series for a 2-D chart is 32,000....".

Cam someone please help?

[austenr]

Have a look at this:

http://www.bmsltd.co.uk/Excel/Default.htm

[rbrhodes]

Hi LucasLondon,

If it's just the labels you're trying to update try:



Sub Update_Chart()

ActiveSheet.ChartObjects(1).Activate

Set startseries = Range("e770").End(xlUp).Offset(-50, 0)
Set endseries = Range("e770").End(xlUp)
Set startlabel = startseries.Offset(0, -4)
Set endlabel = endseries.Offset(0, -4)

'Keep original line
ActiveChart.SetSourceData Source:=Sheets("table").Range(startseries & ":" & endseries), PlotBy:=xlColumns

'Add for X labels
ActiveChart.SeriesCollection(1).XValues = Sheets("table").Range(startseries & ":" & endseries)
End Sub


If it's the error, I think it's because the ranges in your Sub are actually refering to ALL of the columns of the spreadsheet (A to IV = 256).

cheers,

dr