0

I have some VB coding that hides rows above and below the required cell values that will be used as the source data for a simple line chart.

How can I select the remaining (unhidden) cell range as my SetSourceData range ?

I can select the range with the following ...

Range("D6").Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select

... the range always begins in the cell beneath cell C6.

When I try to use this as my SetSourceData range ...

ActiveSheet.Shapes.AddChart.Select

ActiveChart.SetSourceData Source:=Range("D6").Offset(1, 0).Select.Range(Selection, Selection.End(xlDown)).Select

ActiveChart.ChartType = xlLine

... I get the following error:

Run-time error '438': Object doesn't support this property method

Any ideas ?

Thanks

Actually, the following code selects the data I need a little better...

Range("D7", Cells(Rows.count, "D").End(xlUp)).SpecialCells(xlCellTypeVisible).Select

... but, how do I use this as a data source for my chart ?

SMORF
  • 499
  • 6
  • 13
  • 30

1 Answers1

0

Sorry, after creating the more effective select code...

Range("D7", Cells(Rows.count, "D").End(xlUp)).SpecialCells(xlCellTypeVisible).Select

...I realised that all I had to do, to use the code as a chart data source, was remove the '.select' from the code !!!

ActiveSheet.Shapes.AddChart.Select

ActiveChart.SetSourceData Source:=Range("D8", Cells(Rows.count, "D").End(xlUp)).SpecialCells(xlCellTypeVisible)

ActiveChart.ChartType = xlLine

Runs OK now.

SMORF
  • 499
  • 6
  • 13
  • 30
  • You should also take a look at this to learn how to avoid relying on `Select` method. It is probably the #1 cause of errors and makes code much harder to debug: http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros – David Zemens Oct 10 '14 at 14:37