Making a chart's x axis dynamic

[craigwg]

I have a chart that I am creating using VBA. The problem is the x-axis can be different each time the chart is built. For example, sometimes there are 5 values to chart, sometimes 20 values or more.

Currently I have the chart statically building and using all available columns in the spreadsheet. But I need it to only build the x axis based on how many values there are. I hope that makes sense. Here is the part of my code that is in question. There is more above and below, but you get the idea.


With ActiveChart
.ChartType = xlLine
.PlotVisibleOnly = False
.SeriesCollection.NewSeries
With .SeriesCollection(1)
.Name = "='Scrabble'!$H$7"
.Values = "='Scrabble'!$B$10:$S$10" 'This line needs to be dynamic
.Format.Line.Visible = msoCTrue
.Format.Line.ForeColor.RGB = RGB(192, 80, 77) 'Red
End With

[Bob Phillips]

Untested



With ActiveChart

.ChartType = xlLine
.PlotVisibleOnly = False
.SeriesCollection.NewSeries
With .SeriesCollection(1)

.Name = "='Scrabble'!$H$7"
.Values = "='Scrabble'!$B$10:" & Worksheets("Scrabble").Range("B10").End(xlToRight).Address(True, True)
.Format.Line.Visible = msoCTrue
.Format.Line.ForeColor.RGB = RGB(192, 80, 77) 'Red
End With

[craigwg]

That TECHNICALLY works, if row 10 is storing static values. However that row has a function in each cell, which Excel reads as something and the range ends up being exactly where it was before. I tested this by removing the formulas and your solution did work as far as that is concerned.

Is there a way to make this detect values only, rather than formulas?

[Bob Phillips]

Here's an alternative



With ActiveChart

.ChartType = xlLine
.PlotVisibleOnly = False
.SeriesCollection.NewSeries
With .SeriesCollection(1)

.Name = "='Scrabble'!$H$7"
.Values = "='Scrabble'!" & Worksheets("Scrabble").Range("B10").Resize(, Application.Evaluate("SUMPRODUCT(--(LEN('Scrabble'!10:10)>0))")).Address(True, True)
.Format.Line.Visible = msoCTrue
.Format.Line.ForeColor.RGB = RGB(192, 80, 77) 'Red
End With