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]

Let's assume that Column A and Column B contain your data. If the data is bound by empty an empty row and empty column, you can use the CurrentRegion property to define your range..

[FONT=Courier New]    [COLOR=darkblue]Dim[/COLOR] rData [COLOR=darkblue]As[/COLOR] Range
    
    [COLOR=darkblue]Set[/COLOR] rData = Worksheets("Sheet1").Range("A1").CurrentRegion[/FONT]

Otherwise, here's an alternative...

[FONT=Courier New]    [COLOR=darkblue]Dim[/COLOR] rData [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] LastRow
    
    [COLOR=darkblue]With[/COLOR] Worksheets("Sheet1")
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        [COLOR=darkblue]Set[/COLOR] rData = .Range("A1:B" & LastRow)
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR][/FONT]

To propmpt the user for a chart title, you can use the InputBox function...

[FONT=Courier New]    [COLOR=darkblue]Dim[/COLOR] sTitle [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    
    sTitle = InputBox("Enter the chart title...", "Chart Title")
    [COLOR=darkblue]If[/COLOR] Len(sTitle) = 0 [COLOR=darkblue]Then[/COLOR]
        MsgBox "User cancelled...", vbInformation [COLOR=green]'optional[/COLOR]
        [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR][/FONT]

Hope this helps!

 

[rathalex]

Thank you Domenic! How do I incorporate that range into the xlXYScatterSmoothNoMarkers type of plot. Do i use SetSourceData?

 

[rathalex]

Talking about 2nd code since i cant use current region in my boundaries

 

[Domenic]

Yes, use SetSourceData, like this...

ActiveChart.SetSourceData Source:=rData

or

Worksheets("Sheet1").ChartObjects("Chart 1").Chart.SetSourceData Source:=rData

Note, though, the chart needs to be active for the first one.

 

[rathalex]

For some reason cant get this code to run

Dim rData As Range
Dim LastRow

With Worksheets("Sheet1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set rData = .Range("A1:B" & LastRow)
End With

Is LastRow a String or Range? I think im missing something

 

[Domenic]

Sorry, LastRow should have been declared as Long...

Dim LastRow As Long

When you say that "you can't get this code to run", what do you mean exactly? What happens? Do you get an error? If so, which one and on which line?

 

[Domenic]

Also, did you change the sheet name accordingly?

 

[rathalex]

Keep getting error 424. Object required
Sounds like I am missing something...

This far I have:

Sub Graph()
Charts.Add

Dim rData As Range
Dim LastRow As Long

With Worksheets("Data")
LastRow=.Cells(Rows.Count,"AB").End(xlUp).Row
Set rData=.Range("AB1:AC" & LastRow).Value

ChartType=xlXYScatterSmoothNoMarkers
.ChartObjects("Chart1").SetSourceData Source:=rData
.PlotArea.ClearFormats
.HasTittle=True
.ChartTitle.Select
.ChartTitle.Text = "Data"

 

[rathalex]

Actually it does make a chart,but selects first 5-6columns instead of the ones im looking for