[VBA Excel] PivotTable SourceData - Union of Ranges

[Earlien]

Hi. I am having trouble specifying a range as the data source for a pivot cache. It works fine if I specify a simple range, but fails when I use a union of ranges as a range. I am getting a run-time error 1004: "Reference is not valid".

Below is a dumbed-down extract of my code. The first pivot-table works. The second one using a union of ranges causes the error.

Private Sub Test()

    Dim My_Range(1 To 3) As Range
    
    With ThisWorkbook.Worksheets(1)
        Set My_Range(1) = .Range(.Cells(1, 1), .Cells(1, 5))        'Header row
        Set My_Range(2) = .Range(.Cells(1, 1), .Cells(10, 5))      'Data section 1
        Set My_Range(3) = .Range(.Cells(11, 1), .Cells(20, 5))   'Data section 2
    End With
    
    'Add basic pivot table
    ThisWorkbook.Worksheets(2).Activate
    
    With ThisWorkbook.Worksheets(2)
        ThisWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=My_Range(2)).CreatePivotTable _
        TableDestination:=.Cells(3, 1), TableName:="Basic_PivotTable", _
        DefaultVersion:=xlPivotTableVersion10
    End With
    
    'Add pivot table with union of ranges
    ThisWorkbook.Worksheets(3).Activate
    
    With ThisWorkbook.Worksheets(3)
        ThisWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
        SourceData:=Union(My_Range(1), My_Range(3))).CreatePivotTable _
        TableDestination:=.Cells(3, 1), TableName:="Union_PivotTable", _
        DefaultVersion:=xlPivotTableVersion10
    End With
        
End Sub

I cannot find any documentation which suggests that using a union of ranges is not allowed. I've seen a union of ranges get used successfully for things like charts, so I don't see why this would be an issue here.

EDIT: To test the code, you may have to put in some dummy values into worksheet 1 as it does not accept empty ranges.