vba set source data in Excel
Q178. In Excel, how can I set the Source Data of charts using VBA?
For example, I want to dynamically specify the data range for my chart using VBA.
To do it in Excel, here is the answer:
- Option Explicit
- Sub SetChartSourceData()
- ActiveSheet.ChartObjects("Chart1").Chart.SetSourceData Source:=ActiveSheet.Range("$A$1:$B$10")
- End Sub
a) Line 3 - Sets the source data range for "Chart1" to "$A$1:$B$10".
b) If the data is non-contiguous (say X-Axis labels are in column A and Y-Axis values to be plotted are in column D), the 2 ranges have to be specified separately seperated by a comma as in "$A$1:$A$10,$D$1:$D$10".