Selecting chart data

sobeitjedi

Board Regular
Joined
Mar 13, 2006
Messages
235
Office Version
  1. 365
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?
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

severynm

Active Member
Joined
Jan 8, 2021
Messages
260
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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.
 

Forum statistics

Threads
1,147,497
Messages
5,741,499
Members
423,662
Latest member
Ajmal Khursand

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top