How to delete the empty series on a legend?

[dboone25]

hi, I am having a few difficulties using the xlXYScaller graph in where it adds in three blank series into the graph. Unsure how this works but this series indicates in the legend and looks a bit of a mess.

I have been trying to use this code:

Dim c As Chart: Set c = activechart
Dim i As Integer
Dim a As Variant
c.HasLegend = False
c.HasLegend = True
n = 0 'Counter
For i = 1 To 6
a = c.SeriesCollection(i).Values
If WorksheetFunction.Sum(a) = 0 Then
c.Legend.LegendEntries(i-n).Delete 'Added in Counter
n = n+1 'Counter
End If
Next i

It gives me an error under '

If Worksheetfunction.Sum(a) = 0

'Run-time error '13': Type mismatch


Not sure how to get around this or perhaps there is an easier way, thank you.

 

[dboone25]

Many thanks for your help with this.

How do you call a second macro as you said?

 

[dboone25]

I have taken your code directly into excel and have it exactly the same but it now throws an error:

Complie error:

Invalid or unqualified reference

This is at

For j = .SeriesCollection(z).Points.count To 1 Step -1

Should this include

c.SeriesCollection(z)

I have tried putting in the 'c' before .SeriesCollection and it throws a weird error:

Run-time error '5':

Invalid procedure call or argument.

Very strange.

I have not changed anything.Kept it exactly as you sent it.

 

[DushiPunda]

Yea, like I said...I wasn't able to test the code that I provided, which was taken from another site...so it's very possible it doesn't even work. I don't have any data to throw into a spreadsheet to test it with. It does need to have the "c." in there.

As for your other question regarding calling a second macro - exactly as I have it written:

Call MacroName

Like I've mentioned before, I don't work at all with charts so this is all foreign to me. So, if you can provide me with some data, any data, that I can throw into a spreadsheet in the same locations that your data exists, then I'd be able to run your code and get the same results that you're getting...from there, I can help out a lot more than I have been and it will eliminate a lot of the guessing games.

 

[dboone25]

Have a look at the data set im working with:


Data starts across in "B40" and ends in "V40" The data set ends in row 100. There is a gap inbetween 'stop' and 'no' only only column width..I hope that helps...let me know if you need anything else please ask..

no type date_time ref info Chann def frequency amp stop no type date_time ref info Chann def frequency amp stop 403 NX 08/12/2011 11:51 GND YY3 Output Information Machine number 2 15000 - 40000 Hz 57.19129944 34903.47076 404 NX 08/12/2011 11:51 GND YY4 Output Information Machine number 2 15000 - 40000 Hz 57.19129944 34903.47076 403 NX 19/04/2012 10:30 GND YY3 Output Information Machine number 3 15001 - 40000 Hz 42.69160843 34903.47076 404 NX 19/04/2012 10:30 GND YY5 Output Information Machine number 3 15001 - 40000 Hz 42.69160843 34903.47076 403 NX 26/04/2012 12:36 GND YY3 Output Information Machine number 4 15002 - 40000 Hz 45.25019836 34903.47076 404 NX 26/04/2012 12:36 GND YY6 Output Information Machine number 4 15002 - 40000 Hz 45.25019836 34903.47076 403 NX 04/05/2012 10:48 GND YY3 Output Information Machine number 5 15003 - 40000 Hz 51.93323517 34903.47076 404 NX 04/05/2012 10:48 GND YY7 Output Information Machine number 5 15003 - 40000 Hz 51.93323517 34903.47076 403 NX 23/05/2012 20:28 GND YY3 Output Information Machine number 6 15004 - 40000 Hz 60.70976257 34903.47076 404 NX 23/05/2012 20:28 GND YY8 Output Information Machine number 6 15004 - 40000 Hz 60.70976257 34903.47076 403 NX 25/05/2012 09:55 GND YY3 Output Information Machine number 7 15005 - 40000 Hz 48.78887558 34903.47076 404 NX 25/05/2012 09:55 GND YY9 Output Information Machine number 7 15005 - 40000 Hz 48.78887558 34903.47076 403 NX 28/05/2012 12:13 GND YY3 Output Information Machine number 8 15006 - 40000 Hz 80.35152435 34903.47076 404 NX 28/05/2012 12:13 GND YY10 Output Information Machine number 8 15006 - 40000 Hz 80.35152435 34903.47076 403 NX 19/06/2012 13:42 GND YY3 Output Information Machine number 9 15007 - 40000 Hz 753.9284668 34903.47076 404 NX 19/06/2012 13:42 GND YY11 Output Information Machine number 9 15007 - 40000 Hz 753.9284668 34903.47076 403 NX 19/06/2012 16:27 GND YY3 Output Information Machine number 10 15008 - 40000 Hz 44.64014816 34903.47076 404 NX 19/06/2012 16:27 GND YY12 Output Information Machine number 10 15008 - 40000 Hz 44.64014816 34903.47076 403 NX 20/06/2012 09:26 GND YY3 Output Information Machine number 11 15009 - 40000 Hz 40.14241409 34903.47076 404 NX 20/06/2012 09:26 GND YY13 Output Information Machine number 11 15009 - 40000 Hz 40.14241409 34903.47076 403 NX 22/06/2012 13:02 GND YY3 Output Information Machine number 12 15010 - 40000 Hz 58.81702805 34903.47076 404 NX 22/06/2012 13:02 GND YY14 Output Information Machine number 12 15010 - 40000 Hz 58.81702805 34903.47076 403 NX 22/06/2012 16:11 GND YY3 Output Information Machine number 13 15011 - 40000 Hz 37.56629562 34903.47076 404 NX 22/06/2012 16:11 GND YY15 Output Information Machine number 13 15011 - 40000 Hz 37.56629562 34903.47076 403 NX 25/06/2012 10:36 GND YY3 Output Information Machine number 14 15012 - 40000 Hz 147.1986237 34903.47076 404 NX 25/06/2012 10:36 GND YY16 Output Information Machine number 14 15012 - 40000 Hz 147.1986237 34903.47076 403 NX 02/07/2012 17:00 GND YY3 Output Information Machine number 15 15013 - 40000 Hz 61.02352142 34903.47076 404 NX 02/07/2012 17:00 GND YY17 Output Information Machine number 15 15013 - 40000 Hz 61.02352142 34903.47076 403 NX 03/07/2012 14:06 GND YY3 Output Information Machine number 16 15014 - 40000 Hz 45.70903397 34903.47076 404 NX 03/07/2012 14:06 GND YY18 Output Information Machine number 16 15014 - 40000 Hz 45.70903397 34903.47076 403 NX 06/07/2012 11:13 GND YY3 Output Information Machine number 17 15015 - 40000 Hz 40.16555023 34903.47076 404 NX 06/07/2012 11:13 GND YY19 Output Information Machine number 17 15015 - 40000 Hz 40.16555023 34903.47076 403 NX 06/07/2012 13:41 GND YY3 Output Information Machine number 18 15016 - 40000 Hz 91.53705597 34903.47076 404 NX 06/07/2012 13:41 GND YY20 Output Information Machine number 18 15016 - 40000 Hz 91.53705597 34903.47076 403 NX 09/07/2012 10:05 GND YY3 Output Information Machine number 19 15017 - 40000 Hz 84.88558197 34903.47076 404 NX 09/07/2012 10:05 GND YY21 Output Information Machine number 19 15017 - 40000 Hz 84.88558197 34903.47076 403 NX 09/07/2012 13:05 GND YY3 Output Information Machine number 20 15018 - 40000 Hz 50.53219604 34903.47076 404 NX 09/07/2012 13:05 GND YY22 Output Information Machine number 20 15018 - 40000 Hz 50.53219604 34903.47076 403 NX 10/07/2012 09:27 GND YY3 Output Information Machine number 21 15019 - 40000 Hz 63.05794144 34903.47076 404 NX 10/07/2012 09:27 GND YY23 Output Information Machine number 21 15019 - 40000 Hz 63.05794144 34903.47076 403 NX 10/07/2012 12:41 GND YY3 Output Information Machine number 22 15020 - 40000 Hz 40.88444138 34903.47076 404 NX 10/07/2012 12:41 GND YY24 Output Information Machine number 22 15020 - 40000 Hz 40.88444138 34903.47076 403 NX 12/07/2012 10:18 GND YY3 Output Information Machine number 23 15021 - 40000 Hz 43.50764847 34903.47076 404 NX 12/07/2012 10:18 GND YY25 Output Information Machine number 23 15021 - 40000 Hz 43.50764847 34903.47076 403 NX 19/07/2012 09:15 GND YY3 Output Information Machine number 24 15022 - 40000 Hz 475.9176331 34903.47076 404 NX 19/07/2012 09:15 GND YY26 Output Information Machine number 24 15022 - 40000 Hz 475.9176331 34903.47076 403 NX 24/07/2012 15:19 GND YY3 Output Information Machine number 25 15023 - 40000 Hz 45.0959053 34903.47076 404 NX 24/07/2012 15:19 GND YY27 Output Information Machine number 25 15023 - 40000 Hz 45.0959053 34903.47076 403 NX 10/08/2012 15:57 GND YY3 Output Information Machine number 26 15024 - 40000 Hz 39.42825317 34903.47076 404 NX 10/08/2012 15:57 GND YY28 Output Information Machine number 26 15024 - 40000 Hz 39.42825317 34903.47076 403 NX 14/08/2012 16:53 GND YY3 Output Information Machine number 27 15025 - 40000 Hz 49.8355484 34903.47076 404 NX 14/08/2012 16:53 GND YY29 Output Information Machine number 27 15025 - 40000 Hz 49.8355484 34903.47076 403 NX 15/08/2012 14:52 GND YY3 Output Information Machine number 28 15026 - 40000 Hz 43.78467178 34903.47076 404 NX 15/08/2012 14:52 GND YY30 Output Information Machine number 28 15026 - 40000 Hz 43.78467178 34903.47076 403 NX 15/08/2012 20:39 GND YY3 Output Information Machine number 29 15027 - 40000 Hz 45.76498795 34903.47076 404 NX 15/08/2012 20:39 GND YY31 Output Information Machine number 29 15027 - 40000 Hz 45.76498795 34903.47076 403 NX 16/08/2012 09:48 GND YY3 Output Information Machine number 30 15028 - 40000 Hz 35.31030655 34903.47076 404 NX 16/08/2012 09:48 GND YY32 Output Information Machine number 30 15028 - 40000 Hz 35.31030655 34903.47076 403 NX 16/08/2012 14:56 GND YY3 Output Information Machine number 31 15029 - 40000 Hz 80.01585388 34903.47076 404 NX 16/08/2012 14:56 GND YY33 Output Information Machine number 31 15029 - 40000 Hz 80.01585388 34903.47076 403 NX 22/08/2012 09:49 GND YY3 Output Information Machine number 32 15030 - 40000 Hz 334.1209106 34903.47076 404 NX 22/08/2012 09:49 GND YY34 Output Information Machine number 32 15030 - 40000 Hz 334.1209106 34903.47076 403 NX 22/08/2012 20:32 GND YY3 Output Information Machine number 33 15031 - 40000 Hz 53.89852905 34903.47076 404 NX 22/08/2012 20:32 GND YY35 Output Information Machine number 33 15031 - 40000 Hz 53.89852905 34903.47076 403 NX 03/09/2012 15:06 GND YY3 Output Information Machine number 34 15032 - 40000 Hz 49.31636047 34903.47076 404 NX 03/09/2012 15:06 GND YY36 Output Information Machine number 34 15032 - 40000 Hz 49.31636047 34903.47076 403 NX 05/09/2012 15:08 GND YY3 Output Information Machine number 35 15033 - 40000 Hz 51.18596649 34903.47076 404 NX 05/09/2012 15:08 GND YY37 Output Information Machine number 35 15033 - 40000 Hz 51.18596649 34903.47076 403 NX 06/09/2012 09:52 GND YY3 Output Information Machine number 36 15034 - 40000 Hz 45.04953003 34903.47076 404 NX 06/09/2012 09:52 GND YY38 Output Information Machine number 36 15034 - 40000 Hz 45.04953003 34903.47076 403 NX 06/09/2012 20:16 GND YY3 Output Information Machine number 37 15035 - 40000 Hz 63.81425095 34903.47076 404 NX 06/09/2012 20:16 GND YY39 Output Information Machine number 37 15035 - 40000 Hz 63.81425095 34903.47076 403 NX 07/09/2012 08:53 GND YY3 Output Information Machine number 38 15036 - 40000 Hz 60.98968124 34903.47076 404 NX 07/09/2012 08:53 GND YY40 Output Information Machine number 38 15036 - 40000 Hz 60.98968124 34903.47076 403 NX 11/09/2012 10:00 GND YY3 Output Information Machine number 39 15037 - 40000 Hz 91.97193146 34903.47076 404 NX 11/09/2012 10:00 GND YY41 Output Information Machine number 39 15037 - 40000 Hz 91.97193146 34903.47076 403 NX 11/09/2012 10:14 GND YY3 Output Information Machine number 40 15038 - 40000 Hz 44.75217819 34903.47076 404 NX 11/09/2012 10:14 GND YY42 Output Information Machine number 40 15038 - 40000 Hz 44.75217819 34903.47076 403 NX 11/09/2012 13:17 GND YY3 Output Information Machine number 41 15039 - 40000 Hz 53.37839127 34903.47076 404 NX 11/09/2012 13:17 GND YY43 Output Information Machine number 41 15039 - 40000 Hz 53.37839127 34903.47076 403 NX 11/09/2012 18:56 GND YY3 Output Information Machine number 42 15040 - 40000 Hz 57.94307327 34903.47076 404 NX 11/09/2012 18:56 GND YY44 Output Information Machine number 42 15040 - 40000 Hz 57.94307327 34903.47076 403 NX 17/09/2012 19:45 GND YY3 Output Information Machine number 43 15041 - 40000 Hz 2535.95752 34903.47076 404 NX 17/09/2012 19:45 GND YY45 Output Information Machine number 43 15041 - 40000 Hz 2535.95752 34903.47076 403 NX 18/09/2012 19:49 GND YY3 Output Information Machine number 44 15042 - 40000 Hz 59.88801956 34903.47076 404 NX 18/09/2012 19:49 GND YY46 Output Information Machine number 44 15042 - 40000 Hz 59.88801956 34903.47076 403 NX 19/09/2012 17:01 GND YY3 Output Information Machine number 45 15043 - 40000 Hz 49.89593887 34903.47076 404 NX 19/09/2012 17:01 GND YY47 Output Information Machine number 45 15043 - 40000 Hz 49.89593887 34903.47076 403 NX 01/10/2012 14:54 GND YY3 Output Information Machine number 46 15044 - 40000 Hz 43.73712921 34903.47076 404 NX 01/10/2012 14:54 GND YY48 Output Information Machine number 46 15044 - 40000 Hz 43.73712921 34903.47076 403 NX 01/10/2012 16:25 GND YY3 Output Information Machine number 47 15045 - 40000 Hz 41.99577332 34903.47076 404 NX 01/10/2012 16:25 GND YY49 Output Information Machine number 47 15045 - 40000 Hz 41.99577332 34903.47076 403 NX 02/10/2012 09:17 GND YY3 Output Information Machine number 48 15046 - 40000 Hz 95.85310364 34903.47076 404 NX 02/10/2012 09:17 GND YY50 Output Information Machine number 48 15046 - 40000 Hz 95.85310364 34903.47076 403 NX 03/10/2012 10:01 GND YY3 Output Information Machine number 49 15047 - 40000 Hz 50.91450882 34903.47076 404 NX 03/10/2012 10:01 GND YY51 Output Information Machine number 49 15047 - 40000 Hz 50.91450882 34903.47076 403 NX 19/10/2012 12:25 GND YY3 Output Information Machine number 50 15048 - 40000 Hz 32.93612671 34903.47076 404 NX 19/10/2012 12:25 GND YY52 Output Information Machine number 50 15048 - 40000 Hz 32.93612671 34903.47076 403 NX 19/10/2012 13:54 GND YY3 Output Information Machine number 51 15049 - 40000 Hz 50.03309631 34903.47076 404 NX 19/10/2012 13:54 GND YY53 Output Information Machine number 51 15049 - 40000 Hz 50.03309631 34903.47076 403 NX 25/10/2012 09:17 GND YY3 Output Information Machine number 52 15050 - 40000 Hz 41.88483429 34903.47076 404 NX 25/10/2012 09:17 GND YY54 Output Information Machine number 52 15050 - 40000 Hz 41.88483429 34903.47076 403 NX 25/10/2012 11:04 GND YY3 Output Information Machine number 53 15051 - 40000 Hz 72.53578186 34903.47076 404 NX 25/10/2012 11:04 GND YY55 Output Information Machine number 53 15051 - 40000 Hz 72.53578186 34903.47076 403 NX 25/10/2012 13:11 GND YY3 Output Information Machine number 54 15052 - 40000 Hz 57.73155212 34903.47076 404 NX 25/10/2012 13:11 GND YY56 Output Information Machine number 54 15052 - 40000 Hz 57.73155212 34903.47076 403 NX 25/10/2012 14:40 GND YY3 Output Information Machine number 55 15053 - 40000 Hz 48.92303848 34903.47076 404 NX 25/10/2012 14:40 GND YY57 Output Information Machine number 55 15053 - 40000 Hz 48.92303848 34903.47076 403 NX 29/10/2012 14:17 GND YY3 Output Information Machine number 56 15054 - 40000 Hz 74.31622314 34903.47076 404 NX 29/10/2012 14:17 GND YY58 Output Information Machine number 56 15054 - 40000 Hz 74.31622314 34903.47076 403 NX 29/10/2012 14:25 GND YY3 Output Information Machine number 57 15055 - 40000 Hz 1130.541626 34903.47076 404 NX 29/10/2012 14:25 GND YY59 Output Information Machine number 57 15055 - 40000 Hz 1130.541626 34903.47076 403 NX 29/10/2012 19:12 GND YY3 Output Information Machine number 58 15056 - 40000 Hz 1987.033813 34903.47076 404 NX 29/10/2012 19:12 GND YY60 Output Information Machine number 58 15056 - 40000 Hz 1987.033813 34903.47076 403 NX 30/10/2012 14:29 GND YY3 Output Information Machine number 59 15057 - 40000 Hz 56.8405571 34903.47076 404 NX 30/10/2012 14:29 GND YY61 Output Information Machine number 59 15057 - 40000 Hz 56.8405571 34903.47076 403 NX 05/11/2012 13:15 GND YY3 Output Information Machine number 60 15058 - 40000 Hz 41.78619766 34903.47076 404 NX 05/11/2012 13:15 GND YY62 Output Information Machine number 60 15058 - 40000 Hz 41.78619766 34903.47076 403 NX 05/11/2012 19:04 GND YY3 Output Information Machine number 61 15059 - 40000 Hz 5145.78125 34903.47076 404 NX 05/11/2012 19:04 GND YY63 Output Information Machine number 61 15059 - 40000 Hz 5145.78125 34903.47076 <tbody> </tbody>

<tbody>
</tbody>

 

[DushiPunda]

Just wanted to let you know that I've seen your post. I'm off of work today, but I'll have a look and do some messing around in excel tomorrow at work.

 

[DushiPunda]

Here's what I used:

Private Sub CommandButton3_Click()
Dim rXVals As Range
Dim rYVals As Range
Dim i As Long

Dim LastRow As Long
Dim Name As Range


With Worksheets("Sheet1")
    LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
    
    If LastRow < 41 Then
        MsgBox "No data found.", vbInformation
        Exit Sub
    End If

    Set rXVals = .Range("C41:C" & LastRow)
    
    Set rYVals = .Range("I41:J" & LastRow)
End With

With ActiveSheet.ChartObjects.Add(Left:=30, Width:=650, Top:=20, Height:=375).Chart
    .ChartType = xlXYScatter

    For i = 1 To rYVals.Columns.Count
        With .SeriesCollection.NewSeries
            .Name = "=Sheet1!$E$41" ' CI
    
            .XValues = rXVals
            .Values = rYVals.Columns(i)
    
            'Edit marker type
            Select Case i
                Case 1
                    .MarkerSize = 5
                    .MarkerStyle = xlMarkerStyleStar
                Case 2
                    .MarkerSize = 5
                    .MarkerStyle = xlMarkerStyleCircle
            End Select
        End With
    Next i
End With
End Sub

I didn't receive any empty series with the data you provided - I only have 2 and they both appear to be not empty. Although I'm sure there's a prettier way to do it, this also allows for different MarkerStyle's for each of the series.

I also changed everything to Sheet1, so you'll need to change it back to Sheet2.