How to create a Union query in EXL 2007

[blair878]

Hello. I'm trying to create a pivot using multiple sheets in EXL 2007 but research has shown a union query would be the best way to do what I need. My data consists of around 4-6 sheets with two - four columns being used and rows can be in excess of 100, 000. A recent report I had showed 310, 569 rows for the month.

While I'm advanced in EXL, I have no idea how to create this. The few times I have tried it has not worked for me and I'm hoping someone can provide some direction.

Thanks!

 

[JLGWhiz]

Hello. I'm trying to create a pivot using multiple sheets in EXL 2007 but research has shown a union query would be the best way to do what I need. My data consists of around 4-6 sheets with two - four columns being used and rows can be in excess of 100, 000. A recent report I had showed 310, 569 rows for the month.

While I'm advanced in EXL, I have no idea how to create this. The few times I have tried it has not worked for me and I'm hoping someone can provide some direction.

Thanks!

This example code creates a union range for two cell groups in different columns and an entire column, then colors those cells in the union range, then removes the color. Once the union range is created, it has properties like a any other range. You can delete it, copy it, paste it, etc.

Code:
Sub testUnion()
Dim sh As Worksheet, uRng As Range
Set sh = Sheets(1)
Set uRng = Union(Range("A2:A10"), Range("D4:D8"), Range("B:B"))
uRng.Interior.ColorIndex = 3
MsgBox "View colors"
uRng.Interior.ColorIndex = xlNone
End Sub
Code: