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)

--
Don Guillett
SalesAid Software
[email protected]
"CLR" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[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" <[email protected]> wrote in message
news:#[email protected]...
> this might help
> lastrow=cells(rows.count,"aa").end(xlup).row
> myrng=Range("AA9:AA" & lastrow&",Ae9:Ae" & lastrow)
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "CLR" <[email protected]> wrote in message
> news:[email protected]...
> > 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]

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

--
Don Guillett
SalesAid Software
[email protected]
"CLR" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:#[email protected]...
> > this might help
> > lastrow=cells(rows.count,"aa").end(xlup).row
> > myrng=Range("AA9:AA" & lastrow&",Ae9:Ae" & lastrow)
> >
> > --
> > Don Guillett
> > SalesAid Software
> > [email protected]
> > "CLR" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> >
> >
>
>

[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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "CLR" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:#[email protected]...
> > > this might help
> > > lastrow=cells(rows.count,"aa").end(xlup).row
> > > myrng=Range("AA9:AA" & lastrow&",Ae9:Ae" & lastrow)
> > >
> > > --
> > > Don Guillett
> > > SalesAid Software
> > > [email protected]
> > > "CLR" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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]

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

--
Don Guillett
SalesAid Software
[email protected]
"CLR" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> > --
> > Don Guillett
> > SalesAid Software
> > [email protected]
> > "CLR" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:#[email protected]...
> > > > this might help
> > > > lastrow=cells(rows.count,"aa").end(xlup).row
> > > > myrng=Range("AA9:AA" & lastrow&",Ae9:Ae" & lastrow)
> > > >
> > > > --
> > > > Don Guillett
> > > > SalesAid Software
> > > > [email protected]
> > > > "CLR" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

[CLR]

Many many thanks Don........

A little tweaking to adapt to my particular environment and your code flies
perfectly. It does just what I asked for....it's don't get much better than
that.!

Thanks again,
Vaya con Dios,
Chuck, CABGx3



"Don Guillett" wrote:

> let me know how it works out. Enjoy your "day off"
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "CLR" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> > > --
> > > Don Guillett
> > > SalesAid Software
> > > [email protected]
> > > "CLR" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > news:#[email protected]...
> > > > > this might help
> > > > > lastrow=cells(rows.count,"aa").end(xlup).row
> > > > > myrng=Range("AA9:AA" & lastrow&",Ae9:Ae" & lastrow)
> > > > >
> > > > > --
> > > > > Don Guillett
> > > > > SalesAid Software
> > > > > [email protected]
> > > > > "CLR" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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]

glad it helped

--
Don Guillett
SalesAid Software
[email protected]
"CLR" <[email protected]> wrote in message
news:[email protected]...
> Many many thanks Don........
>
> A little tweaking to adapt to my particular environment and your code
flies
> perfectly. It does just what I asked for....it's don't get much better
than
> that.!
>
> Thanks again,
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "Don Guillett" wrote:
>
> > let me know how it works out. Enjoy your "day off"
> >
> > --
> > Don Guillett
> > SalesAid Software
> > [email protected]
> > "CLR" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > > >
> > > > --
> > > > Don Guillett
> > > > SalesAid Software
> > > > [email protected]
> > > > "CLR" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in message
> > > > > news:#[email protected]...
> > > > > > this might help
> > > > > > lastrow=cells(rows.count,"aa").end(xlup).row
> > > > > > myrng=Range("AA9:AA" & lastrow&",Ae9:Ae" & lastrow)
> > > > > >
> > > > > > --
> > > > > > Don Guillett
> > > > > > SalesAid Software
> > > > > > [email protected]
> > > > > > "CLR" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > 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
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
> >