abitslow
Technical User
- Apr 25, 2003
- 44
Hi,
I’m setting up a chart for which I would like a macro to automatically define the range of cells it uses for its source. This is dependent on information copied in by a 3rd party and may be anywhere from 1 to 20001 lines of data in two fields. I need the chart to recognise the range and plot a scatter graph by column.
I tried autofiltering the entire range and getting the chart to plot the visible range only. This works till I try adding the necessary Plotby = xlColumns function , at which point it falls over.
'Autofilter
Sheets("Control").Range("A1").AutoFilter _
field:=1, _
Criteria1:="<>"
'Chart to display visible cells only
Sheets("Print_Sheet").ChartObjects(1).Activate
ActiveChart.SeriesCollection.Add _
Source:=Worksheets("Control").Range("a2:b20001")
ActiveChart.PlotVisibleOnly = True
ActiveChart.PlotBy = xlColumns
Any suggestions greatly appreciated.
thanks,
Matt.
I’m setting up a chart for which I would like a macro to automatically define the range of cells it uses for its source. This is dependent on information copied in by a 3rd party and may be anywhere from 1 to 20001 lines of data in two fields. I need the chart to recognise the range and plot a scatter graph by column.
I tried autofiltering the entire range and getting the chart to plot the visible range only. This works till I try adding the necessary Plotby = xlColumns function , at which point it falls over.
'Autofilter
Sheets("Control").Range("A1").AutoFilter _
field:=1, _
Criteria1:="<>"
'Chart to display visible cells only
Sheets("Print_Sheet").ChartObjects(1).Activate
ActiveChart.SeriesCollection.Add _
Source:=Worksheets("Control").Range("a2:b20001")
ActiveChart.PlotVisibleOnly = True
ActiveChart.PlotBy = xlColumns
Any suggestions greatly appreciated.
thanks,
Matt.