VBA Code to Resize a Chart

[sbbarnett]

I'm working with data that can be filtered multiple ways resulting in a variable number of rows of data. I also have a chart driven off of the data.

I want to create a macro that resizes the cahrt based on how many meaningful rows of data are present

I created the VBA code below where S27 contains the number of relevant rows. I want to substitute the row 85 below with the variable 'Counter' and I can't figure out a syntax that works.

Sub Macro1()
'
' Macro1 Macro
'
Counter = Range("S27").Value

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Values = "='All Chart Data'!N27:N85"
ActiveChart.SeriesCollection(2).Values = "='All Chart Data'!O27:O85"
ActiveChart.SeriesCollection(3).Values = "='All Chart Data'!P27:P85"
ActiveChart.SeriesCollection(4).Values = "='All Chart Data'!Q27:Q85"
End Sub

 

[BobUmlas]

Change
ActiveChart.SeriesCollection(1).Values = "='All Chart Data'!N27:N85"
to
ActiveChart.SeriesCollection(1).Values = "='All Chart Data'!N27:N" & range("S27").value

do the same for the other lines

 

[sbbarnett]

Change
ActiveChart.SeriesCollection(1).Values = "='All Chart Data'!N27:N85"
to
ActiveChart.SeriesCollection(1).Values = "='All Chart Data'!N27:N" & range("S27").value

do the same for the other lines

Thank you - I tried this but got: "Run Time Error '1004': Application-Defined or object-defines error" when it reached that step

 

[sbbarnett]

Disregard - I found the extra apostrophe. Worked great - Thanks