Variable X Axis - Generated by Macro/VBA

I am trying to create a chart showing a payment profile with a fixed start date but variable end. Manually, simple, select all cells with a date before the end date and that is X axis, and series data for appropriate cells.

How can I create an X axis range which only goes to the end year? The same process will presumably apply to series data also.

This cannot be so difficult I would have thought. Am I missing something?

Duncan Beech

Duncan,

I have attached a file that shows one way to do this.  It involves clicking a button on the spreadsheet, but you could move it to a worksheet_change event.

You could also edit the code to add a variable start date as well.

Code for the button is:

Sub VariableChartRange()

Dim dateEndDate As Date
Dim intRows As Integer

dateEndDate = Cells(1, 2)

For intRows = 2 To 366
    'This finds the row of maximum date to be charted
    If Cells(intRows, 4) = dateEndDate Then Exit For
Next intRows

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).XValues = "='Sheet1'!$D$2:$D$" & intRows
ActiveChart.SeriesCollection(1).Values = "='Sheet1'!$E$2:$E$" & intRows

End Sub

Hope this helps.

Matthew

Hi,

As this reply moves straight into a vba solution I'm assuming that defining the dataset with an offset formula, or with a dynamic named range will not work.

Is this the case?

Thanks

Giz

Gizmo,

I haven't worked with dynamic named ranges before, but after having reviewed some material, I don't see why the methods you mention would not work.

If I had known about these when I originally posted, it might have been a more elegant solution.  Thanks for the prompt.