Amending Charts when data series is blank

[Colin Mannion]

Hi

I am pulling together some charts where the data plotted is based on a user-selected series of inputs taken from a validation table. There are six potential rows where a user can add a retailer - see data sheet attached. The problem is that if any of the lines are left blank then a series is indicated in the legend with a zero against it. What I would like to do is to have a macro which removes any data series which has a zero from the legend, but then has a reverse macro which will add the data series legend back in for the next time they want to use all six rows for selecting data.

Hope this makes sense. Full sample data attached.

I am using Excel 2010.

Thanks in advance.
Colin

 

[PCosta87]

Hi @Colin Mannion

Please see attached

I used:

Sub Macro1()

    Application.ScreenUpdating = False

    Dim lrow As Integer
    lrow = Range("A33:A38").Find(What:="0", LookIn:=xlValues).Row - 1

    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SetSourceData Source:=Range("'Conversion Data'!$A$32:$AN$" & lrow)
 
    Application.ScreenUpdating = True

End Sub

Sub Macro2()

    Application.ScreenUpdating = False

    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SetSourceData Source:=Range("'Conversion Data'!$A$32:$AN$38")

    Application.ScreenUpdating = True

End Sub

Added two buttons,
Hope it helps

Regards

 

[Colin Mannion]

Thank you - works a treat.

As a matter of interest, are you one of the gurus that can be paid for this kind of help - happy to make a (small) contribution!

Regards
Colin

 

[PCosta87]

Thank you - works a treat.

As a matter of interest, are you one of the gurus that can be paid for this kind of help - happy to make a (small) contribution!

Regards
Colin

You are welcome

About your question... well, no
I like to help, that is all.

Thanks for considering it though, appreciate that!

Regards

 

[Colin Mannion]

Thank you @PCosta87 a it is very kind of you.

Regards
Colin