Store Current Selection As Range Variable.

  • Thread starter Thread starter Tommy
  • Start date Start date
T

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
 
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)
 
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?
 
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)
 
Back
Top