+ Reply to Thread
Results 1 to 4 of 4

SetSourceData with noncontiguous ranges

  1. #1
    Registered User
    Join Date
    12-27-2015
    Location
    Iowa, USA
    MS-Off Ver
    2010
    Posts
    65

    SetSourceData with noncontiguous ranges

    Seems this is a minor syntax issue.

    First example works fine.

    HTML Code: 
    ActiveChart.SetSourceData Source:=Worksheets("MyData").Range("GJ2:GK200")
    Second example technically works but not what I need.

    HTML Code: 
    ActiveChart.SetSourceData Source:=Worksheets("MyData").Range("GJ2:GL200")
    Note 3 columns considered, however, I don't want column GK. Also, I don't want row 200. So I'm trying this....

    HTML Code: 
    ActiveChart.SetSourceData Source:=Worksheets("MyData").Range("GJ2:GJ" & lr, "GL2:GL" & lr)
    ...which appears to plot fine, but looking at the selected data reveals 2 series, one additional for column K. I found something on "union" for noncontiguous ranges and have tried substituting it for "Range" but the syntax is incorrect and/or incomplete. Hopefully someone sees the issue and can point me to the solution.

    Lewis

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2010 primarily
    Posts
    3,044

    Re: SetSourceData with noncontiguous ranges

    To exclude GK the syntax would be:

    Please Login or Register  to view this content.
    Rory
    Days when we raged, we flew off the page
    Such damage was done

  3. #3
    Registered User
    Join Date
    12-27-2015
    Location
    Iowa, USA
    MS-Off Ver
    2010
    Posts
    65

    Re: SetSourceData with noncontiguous ranges

    Thank you Rory. I was able to get the solution, which was the Union method. I'd just neglected identifying the object with it. Here's what I found worked...
    HTML Code: 
    ActiveChart.SetSourceData Source:=Union(Worksheets("MyData").Range("GJ2:GJ" & lr), Worksheets("MyData").Range("GL2:GL" & lr)
    I was probably using Union incorrectly in the first place. Still, I'm looking at your syntax and notice an additional comma in the second term. Is that what breaks the range apart? Your method seems like a little less work. Your help is always appreciated. Thank you.

  4. #4
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2010 primarily
    Posts
    3,044

    Re: SetSourceData with noncontiguous ranges

    Let's say lr is calculated as 100. My syntax resolves to:

    Please Login or Register  to view this content.
    which is one argument passed to the Range property (the address of the discontiguous range). Your original would resolve to:

    Please Login or Register  to view this content.
    which is two arguments passed, since the comma is not inside the quotes. When you pass two arguments to range, you're passing a start range and an end range, and everything in between is included. It's the same as the difference between:

    Please Login or Register  to view this content.
    which is just two cells, and:

    Please Login or Register  to view this content.
    which is actually equivalent to:

    Please Login or Register  to view this content.
    Make sense?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1