Solved: Range Union

[jmenche]

Howdy,

Assume that I have two identical ranges in different worksheets. Is it possible to create a union of these ranges and use it as the source of a pivottable?

I was trying to use the Union method but I think the fact that they are on different sheets is giving me an error so I am looking for a way around this.

[jmenche]

Howdy again,

Change in plans. The union method won't work across sheets so I moved both ranges into the same sheet. However, when I run the following code, the debug only returns the row count of the first range. Can anybody help??

[VBA]Sub PivotData()
Dim rngPvtData As Range

Set rngPvtData = Application.Union(Range("AcctData"), Range("MktData"))
Debug.Print rngPvtData.Rows.Count
End Sub[/VBA]

[Brandtrock]

The Union of two ranges is created as follows:

 Set rngUnion = Application.Union(Range("First"), Range("Second"))

which yields the address:
$A$1:$D$15,$G$1:$N$20

when:
$A$1:$D$15 is the address of First
$G$1:$N$20 is the address of Second

Asking VBA to return the .Rows.Count using the Debug.Print command will return 15 to the Immediate Window as this is the number of rows in the first range. Switching our Set command to:
Set rngUnion = Application.Union(Range("Second"), Range("First"))

will yield the address:
$G$1:$N$20, $A$1:$D$15

Using Debug.Print.Rows.Count on this new range will return 20 to the Immediate Window. Similar results apply to the Debug.Print.Columns.Count command.

Using the following code:

            rngPvtUnion.Select
              With Selection.Interior
                          .ColorIndex = 5
                          .Pattern = xlSolid
              End With

will change the interior color of both individual ranges to blue, so clearly the Union exists and can be acted upon, but not perhaps in every way that you might want to use it.

The union is not a contiguous range of cells, so using it for a Pivot Table will fail. In order to use the data in these two ranges as a Pivot Source, you could cut and paste the two ranges so that they are a contiguous range of cells, use multiple consolidation ranges (I personally hate this method as it is limiting), or use an array you create on the fly (may not be desirable for large data sets as it can slow processing down).

Regards,

[jmenche]

Thanks for the explanation Brandtrock!