Excel VBA - How to select a dynamic range of cells for a chart series collection?

Question

Im trying to define a chart series collection that goes from a determined cell to the end of data (down and right).

I used the following code to set up the labels of the axis:

oGrafico.Chart.SeriesCollection(nNoSerie).XValues = oPestana.Range(oPestana.Cells(46,14), oPestana.Cells(46,43))

I tried to use a similar approach for the data series with assignation of the ranges to the code, but the error "Application-defined or Object-defined error" pops up:

oGrafico.Chart.SeriesCollection(nNoSerie).Values = oPestana.Range(oPestana.Cells(47,14).End(xlToRight),oPestana.Cells(46,47).End(xlDown))

I've also tried with "oPestana.Range" instead of "oPestana.Cells" but i think i'm missing something. Is there any way to set up the data series in a similar way?

Answer 1

Does this work for you?

oGrafico.Chart.SeriesCollection(nNoSerie).Values = oPestana.Cells(47, 14).End(xlToRight).Resize(oPestana.Range(oPestana.Cells(46, 47), oPestana.Cells(46, 47).End(xlDown)).Cells.Count - 1, 1)