+ Reply to Thread
Results 1 to 9 of 9

How to define range for a chart

  1. #1
    Registered User
    Join Date
    04-27-2012
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    45

    How to define range for a chart

    I wish to plot a simple line chart with only last 20 values in column C and G but do not know how to define the range "last 20 rows" because total number of rows differ from 450 to 500 from file to file. Could someone tell me how to define this dynamic range of values, so that I can use the same in a macro to plot charts in 100s of files in different folders.

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: How to define range for a chart

    Hi GDM69,

    See the attached file and press Ctrl + F3 to learn the formula used in defined names.
    dynamic chart - last 20 values.xlsx

    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Registered User
    Join Date
    04-27-2012
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    45

    Re: How to define range for a chart

    Hi:

    Unfortunately, your file is in non-2003 format so I could not see that formula. I am using version 2003. Can you convert the file in 2003 to enable me to understand the formula or alternatively, you can mention the formula in simple text form. In Excel Viewer, Ctrl + F3 is not working. Thanks

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: How to define range for a chart

    Okay.. please find attached the 2003 version (xls)

    dynamic chart - last 20 values.xls


    Regards,
    DILIPandey
    <click on below * if this helps>

  5. #5
    Registered User
    Join Date
    04-27-2012
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    45

    Re: How to define range for a chart

    Hi DILIPandey:

    Thanks for the new file. I could see the range formula now. I will try to use this in my macro to see if I get the desired result. Thanks once again.

    Regards,

    GDM69

  6. #6
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: How to define range for a chart

    you are welcome...

    Suggest you to mark this thread as [SOLVED].. thx

    Regards,
    DILIPandey
    <click on below * if this helps>

  7. #7
    Registered User
    Join Date
    04-27-2012
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    45

    Re: How to define range for a chart

    Hi DILIPandey:

    Unfortunately, I could not use your formula in my code because my code takes the range in a single line. Actually I want the C values on x axis (horizontal) and G values on Y axis (vertical) whereas both the values in your chart are plotted on Y axis. That may be the reason why I could not use your formula. For your information, I am giving the code lines that I use to plot a FULL range chart.

    .SetSourceData Source:=wksSource.Range("C2:C" & LastRow & ",G2:G" & LastRow), PlotBy:=xlColumns

    Last row is defined as follows LastRow = .Cells(.Rows.count, "C").End(xlUp).Row

    I need to change the values appropriately at the red colored places and don't know how to select a partial range that too from the bottom.

    Perhaps you can try plotting the chart the way I have suggested? This may give us the correct formula.

  8. #8
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: How to define range for a chart

    HI GDM69,

    Please upload sample workbook.. thx.




    Regards,
    DILIPandey
    <click on below * if this helps>

  9. #9
    Registered User
    Join Date
    04-27-2012
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    45

    Re: How to define range for a chart

    Hi DILIPandey:

    I am enclosing herewith a sample file. You will also find in it one chart with full range (column C, time values, on horizontal axis and column G, price movements, on vertical axis) which will give you a fair idea of what kind of chart I am expecting. The only difference is that this time, I want to plot the chart only for last 20 values in stead of full column values, to check how the prices moved in the last 15 to 20 minutes of trading session. Thanks once again for your kind interest and assistance. The code line for the range for full values charts I have already given you and I am sure you would be able to tweak it as per our requirement.

    Regards,

    GDM69
    Attached Files Attached Files

  10. #10
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: How to define range for a chart

    Hi GDM69,

    see the attached file where I used defined name as data sources.. press ctrl + F3 to see them
    ADA.xls


    Regards,
    DILIPandey
    <click on below * if this helps>

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1