Source data for a Chart

[Prakash]

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

 

[JLatham]

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.

 

[Kevin B]

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