How to select multiple ranges for a chart in excel vba?

Question

I want to use multiple range in excel for a chart.

but I am getting the following error.

"Object doesn't support this property or method" on the Setsourcedata.

file = ListBox3.ListCount
var = ListBox2.ListCount

Set range1 = Range(Sheets("Vergleich").Cells(27, 1), Sheets("Vergleich").Cells(27 + var, 1))
Set range2 = Range(Sheets("Vergleich").Cells(27, 3), Sheets("Vergleich").Cells(27 + var, 3 + file))

Set range3 = Union(range1, range2)

With Sheets("Vergleich").ChartObjects("Diagramm 4").Activate
    .SetSourceData Source:=ThisWorkbook.Range(range3)
    .PlotBy = xlRows
End With

Thanks in advance!!

Answer 1

Can you try this? The Activate was a problem, and the source range is already defined as a range in your code.

file = ListBox3.ListCount
Var = ListBox2.ListCount

With Sheets("Vergleich")
    Set range1 = .Range(.Cells(27, 1), .Cells(27 + Var, 1))
    Set range2 = .Range(.Cells(27, 3), .Cells(27 + Var, 3 + file))
    Set range3 = Union(range1, range2)
    .ChartObjects("Diagramm 4").Chart.SetSourceData Source:=range3, PlotBy:= xlRows
End With