Union Method
The order of areas in a union'ed range is determined by the order they were
set (assuming non-contiguous & no overlapping areas) and you can change it.
Sub Test()
Dim r As Range, ar As Range
Set r = Union([F3:F6], [C2:D5], [A1:A4])
For Each ar In r.Areas
Debug.Print ar.Address(0, 0)
Next
Set r = Union(r.Areas(3), r.Areas(2), r.Areas(1))
For Each ar In r.Areas
Debug.Print ar.Address(0, 0)
Next
End Sub
In the sample you posted the three areas are same size and contiguous so you
will end up with one area. But you could do this (subject address length
under 255) -
Sub test2()
Dim r As Range, cel As Range
Set r = Range("c1:c4, a1:a4, b1:b4")
For Each cel In r
Debug.Print cel.Address(0, 0)
Next
End Sub
But I see you want to copy/paste your range, if the whole purpose is to
rearrange data then you will still need to loop & process columns in your
range individually.
Regards,
Peter T
"Noah" wrote in message
...
Is it possible to use the Union method (or some other method) to join
together multiple ranges in an order that is different original order of
columns on the worksheet? For example, I would like rng4 in the macro
below
to have column B on the left, column C in the middle, and column A on the
right. Thanks!
Sub macro1()
Sheets("Sheet1").Select
Set rng1 = Range("A1:A100")
Set rng2 = Range("B1:B100")
Set rng3 = Range("C1:C100")
Set rng4 = Application.Union(rng2, rng3, rng1)
rng4.Copy Sheets("Sheet2").Range("A1")
End Sub
|