Replace non-contiguous column range with Offset column range in Chart SeriesCollectio

[sifar786]

I have a Clustered Bar Chart that is feeded Values from a B column of a Listobject Table. The A Column is for the XValues. This ListObject may be filtered by a dropdown selection to only show certain visible rows.


On Workbook_SheetChange event, for Sheet1,


If the dropdown selects "Aggr" then the 3rd argument of the SeriesCollection should become,

=SERIES("Aggr",(Sheet1!$A$2:$A$4,Sheet1!$A$6:$A$10),(Sheet1!$B$2:$B$4,Sheet1!$B$6:$B$10),1)

i.e. select non-contiguous range areas from Column B.


If the dropdown selects "Core" then the 3rd argument of the SeriesCollection should become,

=SERIES("Core",(Sheet1!$A$2:$A$4,Sheet1!$A$6:$A$10),(Sheet1!$D$2:$D$4,Sheet1!$D$6:$D$10),1)

i.e. select non-contiguous range areas from Column D and vice-versa.


I need a VBA function that can handle contiguous and non-contiguous ranges. I have written the below example which works only for contiguous ranges e.g.

=SERIES("Aggr",Sheet1!$A$2:$A$10,Sheet1!$B$2:$B$10,1) ' for Aggr

and

=SERIES("Core",Sheet1!$A$2:$A$10,Sheet1!$D$2:$D$10,1) ' for Core

Here is the code which works if the seriesCollection is as shown above for contiguous ranges.

Select Case Target.Value2
Case is = "Aggr"  'selected Aggr in dropdown
    For Each Srs In oChart.SeriesCollection
        If Srs.Name = "Core" Then
            Var = Split(Srs.Formula, ",")
            Set Rng = ThisWorkbook.Sheets("Sheet1").Range(Var(2)).Offset(0, -2)
            Var(2) = Trim("'" & Mid(Rng.Address(True, True, xlA1, True), InStr(1, Rng.Address(True, True, xlA1, True), "]", vbTextCompare) + 1))
            With Srs
                .Formula = Join(Var, ",")
                .Name = "Aggr"
            End With
            Exit For
        End If
    Next Srs
    Target.Select


Case is = "Core"  'selected Core in dropdown
    For Each Srs In oChart.SeriesCollection
        If Srs.Name = "Aggr" Then
            Var = Split(Srs.Formula, ",")
            Set Rng = ThisWorkbook.Sheets("Sheet1").Range(Var(2)).Offset(0, 2)
            Var(2) = Trim("'" & Mid(Rng.Address(True, True, xlA1, True), InStr(1, Rng.Address(True, True, xlA1, True), "]", vbTextCompare) + 1))
            With Srs
                .Formula = Join(Var, ",")
                .Name = "Core"
            End With
            Exit For
        End If
    Next Srs
    Target.Select
End Select

How can this code be modified to handle 2 or more non-contiguous ranges?