Hi,

I've got some data that is tracking cases until the end of the next year on a monthly basis. Each month that this is run, the number of months reduces by one, causing me to re-source my chart changing its range to one column less.

I'm running VBA for the file anyway, so would like to add something in to alter the range of my chart to not show blank columns when those months are no longer populated.

Unfortunately I can't just do a xlRight to get the range as all possible columns (upto 23 months) are populated with VLookUp values, however I only take the valid values into the chart.

Using R1C1 I can get the appropriate width of the table, but unfortunately the chart isn't just one block of data, the headings are on row 10 with the contents on rows 12-26 (I can't change this layout).

How would I go about setting a VBA job to change the chart range to show just the valid fields?

Many thanks