Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    May 2008
    Location
    Jamestown, Worcestershire, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    variable range selection before plotting (vb 6.3 excel 2003)

    Hello, with a variable range selection, like xlDown, I am having trouble figuring out the syntax to then load that range into the series collection to add the range to a chart. To me, it would be logical to select a range, and then be able to use Selection instead of a literal range in the range field. Can you point the way, or show me how to find it with the vb help, because I'm either not grasping the help, or I'm searching for the wrong keywords. thx.

    Worksheets("sheet1").Activate

    'Select the data that contains the data:
    Range("S1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("S1:S18,U1").Select
    Range("U1").Activate
    Range(Selection, Selection.End(xlDown)).Select

    .SeriesCollection.Add Source:=Sheets("sheet1").Range(Selection), _ 'VB doesn't like this line. Run time erro 1004. Application defined or Object defined error.
    Rowcol:=xlColumns, SeriesLabels:=True, CategoryLabels:=True, Replace:=False

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: variable range selection before plotting (vb 6.3 excel 2003)

    Try

    Dim rng As Range
    With Worksheets("Sheet1")
    Set rng = .Range(.Range("S1"), .Range("S1").End(xlDown))
    Set rng = Union(rng, rng.Offset(0, 2))
    End With
    .SeriesCollection.Add Source:=rng, _
    Rowcol:=xlColumns, SeriesLabels:=True, CategoryLabels:=True, Replace:=False

  3. #3
    New Lounger
    Join Date
    May 2008
    Location
    Jamestown, Worcestershire, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ariable range selection before plotting (vb 6.3 excel 2003)

    Thanks! I'm now able to move on . The solution I like best is:

    BottRow = Worksheets(thissheet).Range("S1").End(xlDown).Row
    xyRange(1) = "S1:S" & BottRow & ",U1:U" & BottRow

    This way, I can feed a variable range into my fixed range variable script. Thanks again!!!!

    Kent

Posting Permissions

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