Hi,

I am trying to use the union method to combine different columns of data into one range. When the data is on the same sheet it works fine but when one of the ranges is on another sheet such as in the case below it generates a "method of union of object global failed" error.

[VBA]
Sub AAAtesting()
Set a = Sheets("sheet2").Cells(2, 3).Resize(120)
Set b = Sheets("sheet2").Cells(2, 4).Resize(120)
Set c = Sheets("Other").Cells(2, 11).Resize(120)

col = Union(a, b, c)
col.select 'or enter col as an argument into a formula

End Sub
[/VBA]


Internet searches reveal this is because the union function does not work accross different sheets. Is there an alternative way I can acheive what I want either using a modified union or another way to combine ranges?

Thanks,

Hamond