set range for chart

[houde]

Hello to everyone!
I tried to write a macro that re-sets the data range of my chart in Excel to the ith row. i is defined in cell G2. I don't know why my code (see below) isn't working (I get an error "Method Cells of Object _Global failed"). Can anyone help? I would very much appreciate it! Thank you! Fabian

Sub chart_range()
Dim i As Integer
i = Range("G2").Value
Worksheets("sheet1").ChartObjects(1).Activate
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range(Cells(2, 1), Cells(i, 4)), _
PlotBy:=xlRows

End Sub

 

[GlennUK]

You need to change your statement to look like this :

ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range(Activesheet.Cells(2, 1), Activesheet.Cells(i, 4)), _
PlotBy:=xlRows

although you could completely change the range specification to something like this:

ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A2" & i), _
PlotBy:=xlRows

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[houde]

Hey Glenn,
Thank you very much for your help! I did try the " and & but must have put it wrong, something like ("A2"&i&""), which obviously doesn't work. Well, now it should work. Thanks again! Fabian

 

[SkipVought]

You don't need any code to make DYNAMIC RANGES.

How can I rename a table as it changes size faq68-1331

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}