How do I dynamically change a selection range for a graph in vba

Question

This is probably a stupid question but I only recently started working with VBA. I want to plot a graph. My problem is selecting the range. At the moment I have 29 rows, but this will constantly be changing. I will always have two columns but the rows of my data set will not be constant. How can I change my range selection to update based on a cell value? I can probably use count to count the number of rows, but I'm not sure how to specify a range based on a cell value

ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range("I1:J29")

Find the last row as show [here](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba/11169920#11169920) to construct your range and then use that. — Siddharth Rout, Aug 02 '19 at 08:00

score 1 · Answer 1 · edited Aug 02 '19 at 08:12

You could use the below method:

Option Explicit

Sub test()
    Dim LastRowI As Long, LastRowJ As Long, LastRowMax As Long

    'Change sheet name if needed
    With ThisWorkbook.Worksheets("Sheet1")
        'Find the last of row of column I.
        LastRowI = .Cells(.Rows.Count, "I").End(xlUp).Row
        'Find the last of row of column J.
        LastRowJ = .Cells(.Rows.Count, "J").End(xlUp).Row
        'Find the maximun of LastRowI & LastRowJ in case you missing values
        LastRowMax = Application.Max(LastRowI, LastRowJ)
        'I used .Select to mark the range in order to ensure that you use the correct range
        .Range("I1:J" & LastRowMax).Select            
    End With  
End Sub

score 0 · Accepted Answer · answered Aug 02 '19 at 07:50

0

Yes, you can count the number of rows and assign that number as a variable.

Dim Num_Rows as Integer

Num_Rows = Application.WorksheetFunction.CountA(Range("J:J"))

ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range("I1:J" & Num_Rows)

answered Aug 02 '19 at 07:50

Gangula

5,193
4
30
59

Bad Idea to use `CountA` What if there are blank cells in between? – Siddharth Rout Aug 02 '19 at 08:00
@SiddharthRout luckily in this case there won't ever be blank cells – Angie Aug 02 '19 at 08:08

How do I dynamically change a selection range for a graph in vba

2 Answers2