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