-
VBA to add series to graph only if data in column.
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" & 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)
-
You can try putting an if statement before each series: e.g
[VBA]If worksheetfunction.counta(Range("I2:I" & lastrow3)) <> 0 then
[/VBA][VBA]ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "=""R2"""
ActiveChart.SeriesCollection(2).Values = ("='Reads'!I2:I" & lastRow3)
endif
[/VBA]
Note I have not tested this,
Last edited by offthelip; 09-26-2016 at 03:59 AM.
-
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" & 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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules