Automatically resizing charts to align with the last row

I have a macro that filters data and then copies it to a new sheet featuring charts (the number of rows of data changes with each loop). This is then published as a pdf. Is there a way to set one of the charts to automatically resize so that the bottom of it is aligned with the last row of data?

Thank you

Something like this perhaps.

Please Login or Register  to view this content.

Assumes data in column A and positions first chartobject