Select Data to build a graph in macro

[sobeitjedi]

Hi.

I have data in columns A&B which I want to use to build a graph using a macro. At the moment, to select the data I want to use, I use:
Columns("A:B").Select

This works when building the graph step-by-step - it uses only the data in columns A&B as far as the rows go down. The row count will vary in length each time I run it.

The problem is, when I run the macro, it doesn't work - it's selecting all columns A&B, not just where data is available.

So my question is, how do I select all data, where it is available, in A&B in macro code?

 

[Domenic]

You can use the CurrentRegion property of the Range object. Assuming that the worksheet containing the data is the active sheet, try...

    Dim sourceRange As Range
    Set sourceRange = Range("A1").CurrentRegion

However, if your actual data extends beyond Column B and you want to limit your range to Columns A and B, try...

    Dim sourceRange As Range
    Set sourceRange = Range("A1").CurrentRegion.Resize(, 2)

Although, here's an alternative...

    Dim lastRow As Long
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    Dim sourceRange As Range
    Set sourceRange = Range("A1:B" & lastRow)

Now you can assign the range to your chart as follows...

    ActiveSheet.ChartObjects("Chart 1").Chart.SetSourceData sourceRange

Change the chart name accordingly.

Hope this helps!