Selecting two different cells with Range in a loop

Question

I'm trying to add series into a chart with for loop, getting data from a sheet.

(Variable Counter is the counter of the for loop.)

Set Range1 = ThisWorkbook.Sheets("Risiken & Chancen").Range(Cells(Counter, 3), Cells(Counter, 6))
Set Range2 = ThisWorkbook.Sheets("Risiken & Chancen").Range(Cells(Counter, 2), Cells(Counter, 5))

With Sheets("Risikomatrix-Soll-Ist").SeriesCollection.NewSeries
    .Name = "Risiko #" & Counter
    .XValues = Range1
    .Values = Range2
    .Format.Line.DashStyle = msoLineSolid
End With

Using Range(Cells(etc.) , Cells(etc.)) I would like to select two different cells(for example A4;B7), not an area. This code above selects the all cells between the given range.

Answer 1

Use Union for this.

for example:

Union(Range("A4"),Range("B7")).select

Answer 2

You could see:

Dim rngUnion As Range

With ThisWorkbook.Sheets("Risiken & Chancen") 'Create a with statement referring the sheet you want
    'Use "." before Cells & Range in order to include the range in the sheet of the with statement
    Set Range1 = .Range(.Cells(Counter, 3), .Cells(Counter, 6))
    Set Range2 = .Range(.Cells(Counter, 2), .Cells(Counter, 5))
    'Create a union range
    Set rngUnion = Union(.Cells(4, 1), .Cells(7, 2))
End With

With Sheets("Risikomatrix-Soll-Ist").SeriesCollection.NewSeries
    .Name = "Risiko #" & Counter
    .XValues = Range1
    .Values = Range2
    .Format.Line.DashStyle = msoLineSolid
End With