Announcement

Collapse
No announcement yet.

Set Chart Source To Non Contiguous Range Variables

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Set Chart Source To Non Contiguous Range Variables



    Searched but could not find resolution, please help if possible.

    I am trying to create a macro that will graph series or ranges that is declared. The need to declare the range comes from the fact that the source sheet is updated and grows by the day therefore I cannot write a code using fixed a data range, or else the graph won't update.

    Code:
        Dim LastRow As Integer
        Dim RNG1 As Range, RNG2 As Range, RNG3 As Range
        
        Sheets("Summary").Select
        LastRow = Range("B65536").End(xlUp).Row
        
        Set RNG1 = Range(Cells(LastRow - 90, 2), Cells(LastRow, 2))
        Set RNG2 = Range(Cells(LastRow - 90, 10), Cells(LastRow, 10))
        Set RNG3 = Range(Cells(LastRow - 90, 12), Cells(LastRow, 12))
        
        Charts.Add
        ActiveChart.ChartType = xlArea
        ActiveChart.SetSourceData Source:=Range("RNG1, RNG2, RNG3"), PlotBy:=xlColumns
    I'm having problems with the last line.

    The code is supposed to identify the last row and use the 90 data points leading up to the last row to create a series graph.

    Thanks for your help in advance!!

  • #2


    Re: Chart With Declared Ranges (vba)

    Maybe you may try to use dynamic named ranges (eg. if you want to select the 90 last cells of columns B, use the following formula) :
    =OFFSET(Summary!$B$1,COUNTA(Summary!$B:$B)-90,,90)
    there must be no empty cells between the first used cell and the last used cell of the column.
    Then you can use the named ranges into the chart in lieu of ranges.
    HTH
    Daniel
    Auto Merged Post Until 24 Hrs Passes;

    PS.
    To correct your syntax :
    Code:
    ActiveChart.SetSourceData Source:=Union(RNG1, RNG2, RNG3), PlotBy:=xlColumns
    Daniel
    Last edited by Dave Hawley; May 17th, 2008, 08:26.
    Regards.
    Daniel

    Comment

    Working...
    X