Source data for a Chart
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 |
Source data for a Chart
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 |
Source data for a Chart
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 |
All times are GMT +1. The time now is 11:13 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com