VBA: Code for adding dynamic chart data

[vlad7984]

Hello, I am working on a macro to create a chart. When I use the macro recorder and add in the chart data, it seems to use the fixed cell range but what I am actually doing is using ctrl + shift + down to select the entire range which will vary depending on the chart.

How can I get excel to check for the range before adding the chart data? I assume a for loop checking the number of rows would be one way to do it but just want to see if there's any simpler command.

 

[Domenic]

Try using the CurrentRegion property of the Range object, for example, assuming that your data starts at cell A1...

    Dim rSourceData As Range    
    Set rSourceData = Range("a1").CurrentRegion

Hope this helps!

 

[vlad7984]

Thanks! I was able to do this by finding the last row in column A:
lRow = Cells(Rows.Count, "A").End(xlUp).Row


Set Chart_Range = Sheets("Data").Range(Cells(1, "A"), Cells(lRow, "E"))

Have not heard of current region but it looks like it can be useful in the future. The problem was that I didn't want every single column, which from my understanding is what current region does.

 

[Domenic]

Yes, as per the VBA reference found here...

"The current region is a range bounded by any combination of blank rows and blank columns."

So yes, it will include all columns. But, if you only want the first 5 columns, you can use the Resize property...

Set Chart_Range = Sheets("Data").Range("a1").CurrentRegion.Resize(, 5)