Dynamic series range from AutoFilter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
this might help
lastrow=cells(rows.count,"aa").end(xlup).row
myrng=Range("AA9:AA" & lastrow&",Ae9:Ae" & lastrow)
 
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
 
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
 
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
 
Back
Top