Plot xy scatter line up to nonempty cell

[rathalex]

Hello,
I am working to create xy scatter line graph using two data columns. Since data ranges change occasionally, i need to plot up to last non empty cells. Any advice how to approach this? Also, is there way to create a message box that will ask for a chart title? Im am very new to VBA,so dont know all the tricks yet. Thank in advance all!

 

[Domenic]

There are a number of problems with the posted code. Try it like this...

[FONT=Courier New][COLOR=darkblue]Sub[/COLOR] Graph()

    [COLOR=darkblue]Dim[/COLOR] rData [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] LastRow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=darkblue]With[/COLOR] Worksheets("Data")
        LastRow = .Cells(.Rows.Count, "AB").End(xlUp).Row
        [COLOR=darkblue]Set[/COLOR] rData = .Range("AB1:AC" & LastRow)
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    [COLOR=darkblue]With[/COLOR] Charts.Add
        .ChartType = xlXYScatterSmoothNoMarkers
        .SetSourceData Source:=rData
        .SetElement msoElementChartTitleAboveChart
        .ChartTitle.Text = "Data"
        .PlotArea.ClearFormats
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR][/FONT]

Also, make sure that you pay close attention to the dots (.) in the code.

Hope this helps!

 

[rathalex]

Thank you very much Domenic! That code is perfect! I would still have to add inputfunction for thr title message box. I can just simply add it to Charts section correct? without making a new With statement?

 

[Domenic]

With the following code, if the user enters a title, the title will be added to the chart. However, if the user clicks on Cancel, the title is removed.

[FONT=Courier New][COLOR=darkblue]Sub[/COLOR] Graph()

    [COLOR=darkblue]Dim[/COLOR] rData [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] sChartTitle [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] LastRow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    sChartTitle = InputBox("Enter the chart title...", "Chart Title")
    
    [COLOR=darkblue]With[/COLOR] Worksheets("Data")
        LastRow = .Cells(.Rows.Count, "AB").End(xlUp).Row
        [COLOR=darkblue]Set[/COLOR] rData = .Range("AB1:AC" & LastRow)
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    [COLOR=darkblue]With[/COLOR] Charts.Add
        .ChartType = xlXYScatterSmoothNoMarkers
        .SetSourceData Source:=rData
        [COLOR=darkblue]If[/COLOR] Len(sChartTitle) > 0 [COLOR=darkblue]Then[/COLOR]
            .SetElement msoElementChartTitleAboveChart
            .ChartTitle.Text = sChartTitle
        [COLOR=darkblue]Else[/COLOR]
            .SetElement msoElementChartTitleNone
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        .PlotArea.ClearFormats
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR][/FONT]

 

[rathalex]

Thank you very much for all the help! I really appreciate that

 

[Domenic]

You're very welcome. Glad I could help.

Cheers!