Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Results 1 to 2 of 2

Thread: Set Chart Source To Non Contiguous Range Variables

  1. #1
    Join Date
    24th April 2008

    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.

    Dim LastRow As Integer 
    Dim RNG1 As Range, RNG2 As Range, RNG3 As Range 
    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)) 
    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!!

    Excel Video Tutorials / Excel Dashboards Reports

  2. #2
    Join Date
    19th March 2008

    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) :
    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.
    Auto Merged Post Until 24 Hrs Passes;

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

    Excel Video Tutorials / Excel Dashboards Reports

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Set Chart Source Range To Dynamic Range Macro Code
    By pwarden in forum Excel General
    Replies: 1
    Last Post: April 3rd, 2008, 10:30
  2. Change Chart Source Range
    By FrieseLu in forum Excel General
    Replies: 7
    Last Post: July 26th, 2007, 12:00
  3. Expanding Range For Chart Source
    By w424637 in forum Excel General
    Replies: 10
    Last Post: July 9th, 2007, 17:00
  4. Define Chart Source Range
    By basicuser in forum Excel General
    Replies: 2
    Last Post: July 22nd, 2006, 17:38
  5. Chart source Range errors using VBA
    By kweebing in forum Excel General
    Replies: 9
    Last Post: January 22nd, 2005, 05:56


Posting Permissions

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