Chart based on dynamic range

Hi all,

I need to make a chart that arranges data from a table (will always be in same place) that will always have the same number of columns, but will have varying number of rows.

The chart is on the "Online Placement Tab" in that attached workbook.

I thought that using the below code I would be able to always select the dynamic range correctly, and that the excel charting would use this dynamic range as its basis, but whether I use pivot chart or normal chart it always sets the range as a definite range, and not a dynamic one, I hope that makes sense.

Dim lLastRow As Long
lLastRow = ActiveSheet.Range("B42").End(xlDown).Row
ActiveSheet.Range("B42:E" & lLastRow).Offset(-1, 0).Select

I also need to have the chart only have impressions delivered and CTR % as its series, with CTR% on a secondary axis. Although I think I have just about got this sussed (each time I ran my test macros the created chart would be one chart number higher then the last). So for example, when I created the macro it would use chart 5 as its reference, but when I ran it the chart it created was chart 6 and it thus didn't work. Is there a way to make the macro smarter?

As is probably obvious by my attempted coding, I am new to VBA and am just trying to learn by doing, so any help you can give would be much appreciated

You could use a dynamic range name to define your chart series. You don't need any VBA to do that. Just define a dynamic range name with the Name Manager.

see her for more details: http://peltiertech.com/Excel/Charts/Dynamics.html