Union Method

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

You have the answer for Union (won't work as you want).

If you data is set up like a database where you have column headers in row1,
you could do this with the Advanced Filter
Sub ABC()
With Worksheets("Sheet1")
Worksheets("Sheet2").Range("A1:C1") = Array(.Range("B1").Value, _
.Range("C1").Value, .Range("A1").Value)
End With
Sheets("Sheet1").Range("A1:C100").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Worksheets("Sheet2").Range("A1:C1"), _
Unique:=False


End Sub

The disadvantage or advantage depending on your desires is that this copies
everything as a value (formulas are lost - results are copied).

--
Regards,
Tom Ogilvy




"Noah" <[email protected]> wrote in message
news:[email protected]...
> 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
>