[VBA Excel] PivotTable SourceData - Union of Ranges

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.

Code:

---

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.

Ok, I've discovered that if I only use the data section range, the header is automatically included in the range, so there is no need to use the union of the header and data range. E.g.

Code:

---


    'Add pivot table with union of ranges
    ThisWorkbook.Worksheets(3).Activate
   
    With ThisWorkbook.Worksheets(3)
        ThisWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
        SourceData:=My_Range(3)).CreatePivotTable _
        TableDestination:=.Cells(3, 1), TableName:="Union_PivotTable", _
        DefaultVersion:=xlPivotTableVersion10
    End With

---

So my immediate problem is solved as I can get around it by omitting the header row from the SourceData range. However, I would like an explanation as to why using the union doesn't work, as I may need to join several disjoint data ranges together later on. I guess an alternative would be to filter out the unwanted rows using the autofilter and use the filtered range, then remove the autofilter when done - does anyone know if this would work? Or are there any other ways around this problem?


EDIT: Above solution no longer appears to be working... :( I'm now getting the run-time error: "The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. if you are changing the name of a PivotTable field, you must type a new name for the field."

Don't know why it worked before....

I've just tested using a filtered range as the data source, but it picks up up the hidden/filtered rows as well. Well, doing it manually anyway - not sure if you can specify only visible rows in the range in VBA.

A union is a range in the form of $B$1:$C$2,$D$1:$E$2. This is not a valid range for a pivot table. You can't even do that if the ranges touch and are the same size.

You are correct that filtering the source data does not affect the output. You will want to apply a filter to the pivot table instead or create an edited table elsewhere as your source. You can apply a filter using PivotField.PivotFilters.Add or by using the PivotField.PivotItems("Name").Visible property. Check the docs.

Your field name error is most likely an empty column name. Double check your range and worksheet the range is referring to.

Thanks dmaruca.

I find it odd that a union of ranges is not a valid range. After all, they are valid for things like data sources for charts. I guess I will have to create an edited table elsewhere as you suggest.