Adding series to XY chart without getting several axis

[Tomas63]

Hello,
I am trying to make monthly charts from a large spreadsheet and intend to assign different macros to different buttons. Depending on which button is pressed each chart should display one up to three series. All with the same x-values. One problem is that the sheets isn't complety filled. I have solved this by changing the charttype. What I haven't been able to solve is that when I have two or more series I get secondary axis both for the x and y-values although the x-values are the same for the series. What I want is only one x-axis at the bottom and one y-axis to the left. Any ideas how to solve this?

Thanks in advance,
Tomas
Here is the code I am using:

'Variables for looping
Dim serieIndex As Integer
Dim monthIndex As Integer
Dim chartSeries As Integer
'What should be displayed in the title before the month
Dim preTitle As String
preTitle = "Flow "
'The number of series in the chart
Dim noOfSeries As Integer
noOfSeries = 2
'The text in the legend
Dim myArray(2) As String
myArray(1) = "Influent"
myArray(2) = "Condensate"
'The column number containing the Y data
Dim myColArray(2) As Integer
myColArray(1) = 32
myColArray(2) = 42
'The starting row number for each month
Dim myRowArray(13) As Integer
myRowArray(1) = 13
'All assignment to myRowArray not included
myRowArray(13) = 378
Dim myMonthArray(12) As String
myMonthArray(1) = "January"
'All assignment to myMonthArray not included
myMonthArray(12) = "December"

For monthIndex = 1 To 12
ActiveSheet.ChartObjects(monthIndex).Activate
ActiveChart.ChartArea.Select
ActiveChart.HasLegend = True
ActiveChart.HasTitle = True

chartSeries = ActiveChart.SeriesCollection.Count
For serieIndex = 1 To chartSeries
ActiveChart.SeriesCollection(serieIndex).ChartType = xlArea
Next serieIndex

While chartSeries > noOfSeries
ActiveChart.SeriesCollection(noOfSeries + 1).Delete
chartSeries = chartSeries - 1
Wend

While chartSeries < noOfSeries
ActiveChart.SeriesCollection.NewSeries
chartSeries = chartSeries + 1
ActiveChart.SeriesCollection(chartSeries).ChartType = xlArea
Wend

'Write the new title in the active chart
ActiveChart.ChartTitle.Text = preTitle & myMonthArray(counter)
'Set the range for x and y values
For serieIndex = 1 To noOfSeries
With ActiveChart.SeriesCollection(serieIndex)
.Name = myArray(serieIndex)
.Values = "=Data!R" & myRowArray(monthIndex) & "C" & myColArray(serieIndex) & ":R" & (myRowArray(monthIndex + 1) - 1) & "C" & myColArray(serieIndex)
.XValues = "=Data!R" & myRowArray(monthIndex) & "C1:R" & (myRowArray(monthIndex + 1) - 1) & "C1"
.AxisGroup = 1
.ChartType = xlXYScatter
End With
Next serieIndex

With ActiveChart.Axes(xlValue)
If .AxisGroup = xlSecondary Then .Delete
End With

ActiveWindow.Visible = False
Windows("Profal.xls").Activate
Next monthIndex

 

[Fat Cat]

Can you just define the range of Y values to add to the graph, not the X values.

eg:

    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection.Add Source:=Sheets("Sheet1").Range("C1:C9"), _
        Rowcol:=xlColumns, SeriesLabels:=False, CategoryLabels:=False, Replace _
        :=False