Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Apr 2001
    Location
    Rockvale, Tennessee, USA
    Posts
    42
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Changing Range (2003)

    I'd like to know how to change a range using vba. When I've wanted to change something (like this) before, I've just concatenated a variable and placed it between the parentheses - but it does not work with Range.

    I want this, as the result.
    ActiveChart.SetSourceData Source:=Sheets("Data").Range("a2:a40,d2:d40"), _
    PlotBy:=xlColumns

    I'm trying to achieve it, like this
    Dim a as integer, b as integer, c as string, d as string, e as string
    a=40
    b=40
    c='"a2:a"
    d=",d2:d"'
    e=concatenate(a & c & d & 4)
    ActiveChart.SetSourceData Source:=Sheets("Data").Range(e), _
    PlotBy:=xlColumns

  2. #2
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing Range (2003)

    Try this:

    c= "a2:a"
    delete the apostrophe, before the sequence
    and
    e = c & a & d & b

  3. #3
    Lounger
    Join Date
    Apr 2001
    Location
    Rockvale, Tennessee, USA
    Posts
    42
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Changing Range (2003)

    Thanks, Servando. I though I'd tried every sort of configuration. Ah, guess I missed that one or maybe I didn't try without Concatenate. Also, I keyed in the wrong sequence for 'e' on my post. Thanks again, John

  4. #4
    Lounger
    Join Date
    Sep 2008
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing Range (2003)

    Rather than concatenating strings to get ranges, you could use the Cells property and the two property version of Range

    <code>Dim lastRowNumber as Long
    Dim myRange as Range
    lastRowNumber = 40
    With Sheets("Data")
    Set myRange = Range(.Cells(2,1), .Cells(lastRowNumber,1))
    End With
    MsgBox myRange.Address: Rem A2:A40</code>

    To get a discontinous range, you could use Application.Union

    <code>'as above
    Set myRange = Application.Union(myRange, myRange.Offset(0,3))

    ActiveChart.SetSourceData Source:=myRange, PlotBy:=xlColumns
    </code>

  5. #5
    Lounger
    Join Date
    Apr 2001
    Location
    Rockvale, Tennessee, USA
    Posts
    42
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Changing Range (2003)

    Thanks, Mike - very interesting. John

Posting Permissions

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