VBA: Code for adding dynamic chart data

vlad7984

New Member
Joined
May 24, 2019
Messages
14
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.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Try using the CurrentRegion property of the Range object, for example, assuming that your data starts at cell A1...

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

Hope this helps!
 
Last edited:
Upvote 0
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.
 
Upvote 0
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...

Code:
Set Chart_Range = Sheets("Data").Range("a1").CurrentRegion.Resize(, 5)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

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
Back
Top