Dynamic series range from AutoFilter

[CLR]

Hi All......

I have code below, that after I autofilter and copy results over to another
sheet, will go to AA9 and AE9 as the first cells in Series1 and Series2
sources...that works fine, except I never know where the bottom of the column
is going to be based on what data is filtered.....when I try to Record the
macro, I only get fixed references....AA9 and AE9 are fine for the top of the
column, but I need somehow to "find" the bottom of the column.......

Existing code:

Sub M034Chart()
'
' M034Chart Macro
' Macro recorded 04/12/2005 by CLR
'

'
Sheets("Reports").Select
Range("AA9:AA51,Ae9:Ae51").Select
Range("Ae9").Activate
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Reports").Range( _
"AA9:AA51,Ae9:Ae51"), PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Reports"
End Sub

Any help would be much appreciated....
Vaya con Dios,
Chuck, CABGx3

 

[Don Guillett]

this might help
lastrow=cells(rows.count,"aa").end(xlup).row
myrng=Range("AA9:AA" & lastrow&",Ae9:Ae" & lastrow)

 

[CLR]

Thank you kind Sir.............I don't know exactly what to do with it, but
I will give it a go, or two or three <g>.....

Vaya con Dios,
Chuck, CABGx3

 

[Don Guillett]

try

Sub dochart()
lastrow = Cells(Rows.Count, "aa").End(xlUp).Row
Set myrng = Range("AA9:AA" & lastrow & ",AE:AE" & lastrow)
Charts.Add
With ActiveChart
.ChartType = xlLineMarkers
.SetSourceData Source:=myrng, PlotBy:=xlColumns
.Location Where:=xlLocationAsObject, Name:="Reports"
End With
Range("aa9").Select
End Sub

 

[CLR]

Thanks Don.......

I'm off today, car wouldn't start, then found I need to take my Lady to the
Doctor and one of the cats to the Vet, etc etc........but will try it
tomorrow at work for sure........

Many thanks,
Vaya con Dios,
Chuck, CABGx3

 

[Don Guillett]

let me know how it works out. Enjoy your "day off"

 

[Don Guillett]

glad it helped