Sub SetChartDataSource()
Dim NewData As String
Dim WhereWasI As String

WhereWasI = ActiveCell.Address
'using "O2" per your information
NewData = "O2:" & Range("O2").End(xlDown).Address
'assumes source data is on same sheet with chart
'change ChartObjects("Chart 1"). to name of your chart
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveChart.SetSourceData Source:=Sheets(ActiveSheet.Name).Range(NewData)
Range(WhereWasI).Select
End Sub

Put a button or other easy access to the Macro on the sheet and it's a done
deal. It will always pick up the area from the start of the data (O2) down
to the last entry in that list above an empty cell.

"Prakash" wrote:

Hi All,

1. I have a coumn whose values (I mean the entries under that column) are
dynamic are exported from an external file. (ex:when exported column may have
14 rows, next time 100 rows, next time 10 tha way it is dynamic)
2. I have an excel chart whose source data is this column for the graph.
3. How can I dynamycally set the source data =Report!$O$2:$O$14 for
4. How can I set the values in the source so that it adjusts to the actual
number of rows with data every time there is a new export
5. User does not want to change the source everytime it is exported.

Thanks for your help.

Regards
Prakash