Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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
    Posts
    3

    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.

    VB:
     
    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!!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    19th March 2008
    Posts
    449

    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 :
    VB:
    ActiveChart.SetSourceData Source:=Union(RNG1, RNG2, RNG3), PlotBy:=xlColumns 
    
    
    Daniel
    Last edited by Dave Hawley; May 17th, 2008 at 09: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. Replies: 1
    Last Post: April 3rd, 2008, 11:30
  2. Change Chart Source Range
    By FrieseLu in forum EXCEL HELP
    Replies: 7
    Last Post: July 26th, 2007, 13:00
  3. Expanding Range For Chart Source
    By w424637 in forum EXCEL HELP
    Replies: 10
    Last Post: July 9th, 2007, 18:00
  4. Define Chart Source Range
    By basicuser in forum EXCEL HELP
    Replies: 2
    Last Post: July 22nd, 2006, 18:38
  5. Chart source Range errors using VBA
    By kweebing in forum EXCEL HELP
    Replies: 9
    Last Post: January 22nd, 2005, 05:56

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
  •  
porno