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

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

The following might help. The code selects cell O2 in Sheet1 and does an
Shift+End+Down to select the column. It then names the range ChartData and
uses the named range as the chart data source:

Range("O2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Name = "ChartData"
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("ChartData"), _
PlotBy:=xlRows

--
Kevin Backmann


"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