Automate chart range dependent upon data present

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

You could put #N/A in the data used for the X axle when the display must not be done: See with a formula versus the time frame used

Maybe post the code where you have that is creating the chart? or is the chart already there and the code is just populating data?

I personally use something like this to create chart:

Please Login or Register  to view this content.

After much messing with code I think I've finally got what I was after.


Sub Update_Monthly_DTV_Chart()

Sheets("Maturity - DTV Monthly").Select

Dim Cols As Integer
Dim Rng1 As Range, Rng2 As Range
Dim NewRng As Range

Cols = ActiveWorkbook.Sheets("Maturity - DTV Monthly").Cells(3, 15).Value - 2

With ActiveWorkbook.Sheets("Maturity - DTV Monthly")
Set Rng1 = .Range(Cells(10, 6), Cells(10, Cols))
Set Rng2 = .Range(Cells(12, 6), Cells(26, Cols))

Set NewRng = .Range(Rng1.Address & "," & Rng2.Address)
End With

Sheets("Maturity Charts").Select
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=Sheets("Maturity - DTV Monthly").Range(NewRng.Address)
End Sub

Thanks