Select Data to build a graph in macro

sobeitjedi

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

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,940
Office Version
  1. 365
Platform
  1. Windows
You can use the CurrentRegion property of the Range object. Assuming that the worksheet containing the data is the active sheet, try...

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

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

Although, here's an alternative...

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

VBA Code:
    ActiveSheet.ChartObjects("Chart 1").Chart.SetSourceData sourceRange

Change the chart name accordingly.

Hope this helps!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,237
Messages
5,768,967
Members
425,507
Latest member
AndreaWorkPlace

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