Solved: Set X-Axis Value to match row numbers

[JimS]

My data has about 61,xxx rows in Column A. Excel Charts have a hard limit of 32,000 data points so I have to split the data in half and use 2 Charts to plot the data points.

I use VBA to create the 2 Charts.

How can I change the X-Axis Values to match the Row Numbers.

The first chart contains rows 1 - 32,000 and the X-Axis Value matches the row numbers, but when I plot rows 32,001 through 61,xxx in the second chart, the X-Axis vaules are 1 - 31,xxx.
I need the X-Axis Values to match the Row Numbers (32,001 - 61,xxxx).

Any ideas?

Thanks...

JimS

[p45cal]

add a column with the formula
=ROW()
and plot that as the x-axis labels.

[JimS]

I prefer to do in VBA if possible.

[p45cal]

show me your existing vba

[JimS]

I went with your suggestion of the = ROW().

Thanks...

[p45cal]

just as I worked out how to do it in vba.. :(

[JimS]

I'm still open to improvements. This is how I did it. Unfortunately the XValues has to reference a Column on the worksheet that can't be hidden.


Sub Macro1()

Dim xLabels As String
Dim lengthA As Long
Dim shtName As Worksheet

Range("A1").Select

lengthA = Worksheets("Sheet1").Range("A65536").End(xlUp).Row


xLabels = "BA32001:BA" & lengthA

Set shtName = Worksheets("Sheet1")

Range("BA1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-52]="""","""",ROW())"
Application.Run "CallCheckEntry"
Range("BA1").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range("A1").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).XValues = shtName.Range(xLabels)
ActiveChart.SeriesCollection(2).XValues = shtName.Range(xLabels)
ActiveChart.Axes(xlCategory).Select
With ActiveChart.Axes(xlCategory)
.CrossesAt = 1
.TickLabelSpacing = 1000
.TickMarkSpacing = 1
.AxisBetweenCategories = True
.ReversePlotOrder = False
End With
End Sub

[p45cal]

This code looks like the version you're using which now has a column dedicated for the X values.
My idea was to use this in your code (tested):
ActiveChart.SeriesCollection(1).XValues = Evaluate("row(32001:" & lengthA & ")") instead of:
ActiveChart.SeriesCollection(1).XValues = shtName.Range(xLabels)
You only need a value for lengthA and you've already got that in your code.

This stems from the likes of these lines all working and tested:
ActiveChart.SeriesCollection(1).xvalues=Evaluate("Row(32:44)")
ActiveChart.SeriesCollection(1).xvalues=[Row(32:44)]
ActiveChart.SeriesCollection(1).xvalues=[Row(Sheet1!$A$50:$A$70)]

You might get away without the second line:
ActiveChart.SeriesCollection(2).XValues = ............
since they're both the same and there's only one set of xvalues to display on the chart?

[JimS]

Thanks, I'll give it a try.

[Andy Pope]

I have to assume that given the +60k data points the chart is a line chart.

If you use an xy-scatter then you can create 1 chart with 2 series.

Series 1 formula
=SERIES(,Sheet1!$A$1:$A$31000,Sheet1!$B$1:$B$31000,1)

series 2 formula
=SERIES(,Sheet1!$A$31001:$A$60000,Sheet1!$B$31001:$B$60000,2)

For me the x-axis had an auto maximum of 70000 but this can be altered.