Ranges - NonAdjacent Cells, varying length

[DanExcel]

Hello,

I'm creating a chart in a macro using the code:

Dim Cht As Chart

Set Cht = Application.Charts.Add
With Cht
.ChartType = xlXYScatter
.SetSourceData Source:=Sheets("2-1").Range(????), PlotBy:=xlColumns
.Location Where:=xlLocationAsObject, Name:="2-1"
End With


The ???? is there because I need to know how to use a range of cells in non-adjacent columns. Specifically, my x data is in column F, and my y data is in column D.
Furthermore, I intend to use this macro on many, many data sets. Columns F and D will have the same number of entries on a given sheet, but each sheet will have a different number of entries.

Can anyone tell me how to enter this range?

Thank you,

-DanExcel

 

[Barrie Davidson]

Would this work for you?

.SetSourceData Source:=Sheets("2-1").Range("D1", Sheets("2-1"). _
    Range("F65536").End(xlUp).Address), PlotBy:=xlColumns

Regards,

 

[DanExcel]

Barrie,

I tried that code as:

.SetSourceData Source:=Sheets("2-1").Range("D1", Sheets("2-1").Range("F65536).End(x1Up).Address), PlotBy:=xlColumns

and I received a compile error. It says expected: list separator or ). Am I missing something?

Thanks for the suggestion!

-DanExcel

 

[Barrie Davidson]

On 2002-03-08 07:45, DanExcel wrote:
Barrie,

I tried that code as:

.SetSourceData Source:=Sheets("2-1").Range("D1", Sheets("2-1").Range("F65536).End(x1Up).Address), PlotBy:=xlColumns

and I received a compile error. It says expected: list separator or ). Am I missing something?

Thanks for the suggestion!

-DanExcel

Dan, you need a quotation mark after F65536. So change your code to:

.SetSourceData Source:=Sheets("2-1").Range("D1", Sheets("2-1"). _
Range("F65536").End(xlUp).Address), PlotBy:=xlColumns

Does that help?

 

[DanExcel]

Wow - I should have noticed that. Sorry.

It compiles now, but when I run it it fails saying that x1Up = Empty. What is x1Up doing?

-DanExcel

 

[Barrie Davidson]

On 2002-03-08 07:56, DanExcel wrote:
Wow - I should have noticed that. Sorry.

It compiles now, but when I run it it fails saying that x1Up = Empty. What is x1Up doing?

-DanExcel

Time for me to say sorry. After properly reading your requirements, I think your code needs to be:

Dim Cht As Chart
Dim Lastrow As Long

Set Cht = Application.Charts.Add
Lastrow = Sheets("2-1"). _
    Range("F65536").Row

With Cht
.ChartType = xlXYScatter
.SetSourceData Source:=Sheets("2-1").Range("D1:D" & Lastrow & _
    ",F1:F" & Lastrow), PlotBy:=xlColumns
.SeriesCollection(1).XValues = "='2-1'!R1C6:R" & Lastrow & "C6"
.SeriesCollection(1).Values = "='2-1'!R1C4:R" & Lastrow & "C4"
.Location Where:=xlLocationAsObject, Name:="2-1"
End With

Are we there yet?


_________________

Barrie Davidson
My Excel Web Page

PS - the xlUp is the equivalent of END+ARROW UP. Gets the address of the first non-blank cell above F65536.
This message was edited by Barrie Davidson on 2002-03-08 08:16

 

[DanExcel]

Thanks Barrie! That code did it, although I couldnt get it to work with the R1C1 style references, so I changed them to A1 style references.

I made a couple small changes. The code I had working, in the end, is pasted below in case anyone else has a similar issue later. (This is just a modified version of Barrie's code).

____________________________________________
Sub SSChart()
Dim Cht As Chart
Dim Lastrow As Long
Dim CurrentSheet As String

CurrentSheet = ActiveSheet.Name


Set Cht = Application.Charts.Add
Lastrow = Sheets(CurrentSheet).Range("F65536").End(xlUp).Row


With Cht
.ChartType = xlXYScatter
.SetSourceData Source:=Sheets(CurrentSheet).Range("F5:F" & Lastrow & ",D5:D" & Lastrow), PlotBy:=xlColumns
.SeriesCollection(1).XValues = Worksheets(CurrentSheet).Range("F5:F" & Lastrow)
.SeriesCollection(1).Values = Worksheets(CurrentSheet).Range("D5:D" & Lastrow)
.Location Where:=xlLocationAsObject, Name:=CurrentSheet
End With

End Sub
____________________________________________