Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Union Method
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" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Union Method | Excel Programming | |||
Union Method with multiple worksheets | Excel Programming | |||
union method for non-adjacent ranges | Excel Programming | |||
union method for non-adjacent ranges | Excel Programming | |||
union method for non-adjacent ranges | Excel Programming |