Chart with non-contiguous X an Y Values

    Hi all,


    I have a chart which represents the values for the last 3 months for a range of locations. The location name is in column 1, the values change from month to month by 1 column right. I use

    Code
    Set DataRange = Range(Activecell.Offset(0,-2), Activecell.Offset(20,))
Set LocationRange = Range(R20C1:R40C1)


    to establish the X and Y values, but the chart appears either with all months or, if I leave the Location out, offers the column next to the first month's data as X values.


    I have got round this for the moment by

    Code
    ActiveChart.SeriesCollection(n).delete


    an appropriate number of times, but this can't be good.


    Can somebody suggest a solution?


    Regards


    Robert

    Re: Chart with non-contiguous X an Y Values


    Hi Andy


    thanks again for your interest. I have attached a sample of the data. Changing the data source manually is no problem, but how do I identify the data range in a macro? If I say


    Code
    Set myRange = Range(LocationRange,DataRange) 'with/without "s
        ActiveChart.SetSourceData Source:=myRange, PlotBy:=xlColumns


    all the current range is included


    Regards


    Robert

    Re: Chart with non-contiguous X an Y Values


    Hi,
    Because the range in non contiguous then you need to use UNION instead of RANGE to get the combined data range.
    Also when the populated chart is active the activecell doesn't work so I explicitly set this via a range variable.
    [vba]Sub x()


    Dim DataRange As Range
    Dim LocationRange As Range
    Dim myRange As Range
    Dim rngActCell As Range

    Set rngActCell = Range("G1")
    Set DataRange = Range(rngActCell.Offset(0, -2), rngActCell.Offset(6))
    Set LocationRange = Range("A1:A7")


    Set myRange = Union(LocationRange, DataRange) 'with/without "s
    ActiveChart.SetSourceData Source:=myRange, PlotBy:=xlColumns


    End Sub[/vba]

    Re: Chart with non-contiguous X an Y Values


    Hi Andy


    I think the problem may have been that I was referring to an ActiveChart where none exitsted. Adding Charts.Add before the line which failed solved the problem


    Thanks again


    Robert

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login