VBA: Dynamic Chart Range dependent on Last Row

[jaspalsd]

Hi,

I've put together a macro with a range of data where the lastrow has been defined.

All is well for the "table" - officially not a table but when I rerun the macro and set the range for the chart it goes to a completely different row

The last row should be row7 - above "TOTAL" and it works fine when rerunning the data for the table but I have no idea why it goes down to Row 27 for the chart.

Using this method

ActiveChart.SetSourceData _
    Source:=.Range("B2" & LastRow  & ":D2" & LastRow )

I could delete the chart with each time the macro is run but then I'll have to amend the formatting in the macro but I could do it this way instead!

I will appreciate any feedback and suggestions on how to fix this

 

[jaspalsd]

no worries

workaround is to make it as a table with spacing for the total row then hide the open row

pie chart range will always look at the table and automatically update the range