Store Current Selection As Range Variable.

[Tommy]

Hi guys,

I have a small subroutine which, when run, will select a range of
cells for me based on certain criteria given to it. Typically I am
left with a selection of data in a single column e.g. AC7:AC100.

My goal is to plot this data as a series in a chart. Currently, the
code I have for my chart is:

Set Chart1 = Worksheets("Tuning").ChartObjects(1)
Chart1.Activate
With Chart1.Chart

' Clear all previous series.

Do Until .SeriesCollection.Count = 0
.SeriesCollection(1).Delete
Loop
.Type = xlLine
.SeriesCollection(1).Values = DataSource1
.SeriesCollection(1).Name = "Tag 1"
.HasTitle = True
.ChartTitle.Text = "Chart 1 Title"
End With


As you can see, it selects the ChartObject(1) called Chart1, deletes
all previous series on the chart and then adds a single series. Here
is where I get the problem. I need that original selection of
AC7:AC100 being stored as a range named DataSource1. I have no idea
however how to store an active/current selection as a range so that
this code will work. If you believe I can do it better another way,
please suggest so. I must note that I have to use ChartObjects as I
have other code that relies on this fact.

Many regards and thanks,

Tom

 

[Bob Phillips]

Set Chart1 = Worksheets("Tuning").ChartObjects(1)
With Chart1.Chart

' Clear all previous series.

Do Until .SeriesCollection.Count = 0
.SeriesCollection(1).Delete
Loop
.Type = xlLine
.SetSourceData Source:=Selection
.SeriesCollection(1).Name = "Tag 1"
.HasTitle = True
.ChartTitle.Text = "Chart 1 Title"
End With


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Tommy]

Whilst this code allows for the setting of the source data, you must
define all source data in a single selection which is not what I wish
to achieve. As I stated before, how do I store the already selected
AC7:AC100 as a range called DataSource1 that I will be able to pass to
SeriesCollection(1).Values?

 

[Bob Phillips]

No, as you stated, ... I am left with a selection of data in a single column
e.g. AC7:AC100

Just change Selection to Datasource1, they are both ranges.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)