VBA to add series to graph only if data in column.

[MattehWoo]

Hi all...

I have the following code but what i have found is that it will always create 4 data series even though there's only data for say 1 or 2 of them.

It's not a major issue for me, but it can confuse other people when the graph shows 4 different series on the right, but there's only 2 lines showing on the graph.

How would i go about having it so that it only shows the legend for the data that is actually there?

I hope i'm making sense. It is Monday morning after all....



Dim lastRow3 As Long
lastRow3 = Range("C" & Rows.Count).End(xlUp).Row

Sheets("Graph").Select

ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Text = "=Front!H5"

ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "=""R1"""

ActiveChart.SeriesCollection(1).Values = ("='Reads'!D2:D" & lastRow3)

ActiveChart.SeriesCollection(1).XValues = ("='Reads'!C2:C" & lastRow3)

ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "=""R2"""

ActiveChart.SeriesCollection(2).Values = ("='Reads'!I2:I" & lastRow3)


ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).Name = "=""R3"""

ActiveChart.SeriesCollection(3).Values = ("='Reads'!N2:N" & lastRow3)

ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(4).Name = "=""R4"""

ActiveChart.SeriesCollection(4).Values = ("='Reads'!S2:S" & lastRow3)

[offthelip]

You can try putting an if statement before each series: e.g

If worksheetfunction.counta(Range("I2:I" & lastrow3)) <> 0 then
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "=""R2"""

ActiveChart.SeriesCollection(2).Values = ("='Reads'!I2:I" & lastRow3)
endif

Note I have not tested this,

[MattehWoo]

Cheers for your help.

From this i managed to get what i wanted:

Sub Graph()

Sheets("Reads").Select

Dim lastRow3 As Long
lastRow3 = Range("C" & Rows.Count).End(xlUp).Row

Sheets("Graph").Select

ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Text = "=Front!H5"

ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "=""R1"""

ActiveChart.SeriesCollection(1).Values = ("='Reads'!D2:D" & lastRow3)

ActiveChart.SeriesCollection(1).XValues = ("='Reads'!C2:C" & lastRow3)

Sheets("Reads").Select
If WorksheetFunction.CountA(Range("I2:I" & lastRow3)) <> 0 Then
Sheets("Graph").Select
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "=""R2"""

ActiveChart.SeriesCollection(2).Values = ("='Reads'!I2:I" & lastRow3)
End If

Sheets("Reads").Select
If WorksheetFunction.CountA(Range("N2:N" & lastRow3)) <> 0 Then
Sheets("Graph").Select
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).Name = "=""R3"""

ActiveChart.SeriesCollection(3).Values = ("='Reads'!N2:N" & lastRow3)
End If

Sheets("Reads").Select
If WorksheetFunction.CountA(Range("S2:S" & lastRow3)) <> 0 Then
Sheets("Graph").Select
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(4).Name = "=""R4"""

ActiveChart.SeriesCollection(4).Values = ("='Reads'!S2:S" & lastRow3)
End If

Sheets("Graph").Select



Sheets("Front").Visible = xlSheetHidden
Sheets("1").Visible = xlSheetHidden
Sheets("2").Visible = xlSheetHidden
Sheets("3").Visible = xlSheetHidden



End Sub