Selecting chart data

[sobeitjedi]

Hi.

I have data in Columns A & B, starting at row 15. I want to use a macro to build a graph, but the number of rows used will vary. How do I do this?

For example, at the moment rows 15 to 41 has the data in columns A&B for my graph. Row 42 onwards is blank. But the next time I import data, it could be 15 to 104 (105 onwards will be blank) in A&B.

Can you please help?

 

[severynm]

Is your data in a Excel table? Theres many reasons I'd recommend doing that, one of which is if you do that and set your chart range to the range of the Table columns, the chart will automatically update if the number of rows in the table changes. The table will automatically expand if you paste more data then there are rows, so the only VBA you'd need is to resize the table if there are fewer data points then there are rows, and that can be done by just counting all the blank rows and resizing the table by that count.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim dataTable As ListObject
    Dim row As Integer
    Dim blankRows As Integer
    Set dataTable = Me.ListObjects("Table2")
    
    For row = 1 To dataTable.DataBodyRange.Rows.count
        If dataTable.ListColumns("Column1").DataBodyRange(row).Value = vbNullString Then
            blankRows = blankRows + 1
        End If
    Next row
    
    dataTable.Resize Me.Range("Table2[#All]").Resize(dataTable.Range.Rows.count - blankRows, dataTable.Range.Columns.count)
End Sub

You'd want to change Table2 to the name of your table, and Column1 to the column that you want to evaluate if blank.