Chart with Dynamic Row and Column Count Help needed

[sdmikejr]

I have searched and while I have found instances that are almost there - I cannot seem to fit them any of them in my VBA solution that work.

Here's the deal. I have test data coming from a machine that where the output of the row and column count can change.

I am trying to use VBA to create a chart, regardless of the column and row count, to plot the results.

Where I am getting stuck is I cannot seem to find an acceptable approach to determine the range being somewhat dynamic.

Can someone please help me?


Sub GetChart()Dim MyChart As Chart
Dim DataRange As Range
Set DataRange = ActiveSheet.Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Set MyChart = ActiveSheet.Shapes.AddChart2.Chart
MyChart.SetSourceData
Source:=DataRange
End Sub

[mancubus]

welcome to the forum.

if it is a contiguous range (ie, row 1 and and column A have no blank cells), you can use:



MyChart.SetSourceData Source:=Worksheets("Sheet1").Range("A1").CurrentRegion

[sdmikejr]

Hi, and thank you for your help.
Would the code look something like this?



Sub GetChart()Dim MyChart As Chart
Dim DataRange As Range
Set DataRange = ActiveSheet.Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Set MyChart = ActiveSheet.Shapes.AddChart2.Chart
MyChart.SetSourceData Source:=Worksheets("Sheet1").Range("A1").CurrentRegion
End Sub

[mancubus]

something like this:



Sub GetChart()

Dim MyChart As Chart

Set MyChart = Worksheets("Sheet1").Shapes.AddChart2.Chart
'Change Sheet1 to sheet name which houses the chart

MyChart.SetSourceData Source:=Worksheets("Sheet1").Range("A1").CurrentRegion
'Change Sheet1 to sheet name which houses the source data

End Sub