Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Ranges - NonAdjacent Cells, varying length

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Would this work for you?

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

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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:

    Code:
    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 ]

  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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
    ____________________________________________

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •