Union Range Order As Written

wsnyder

Board Regular
Joined
Sep 23, 2018
Messages
224
Office Version
  1. 365
Platform
  1. Windows
Hi all,

Using Excel 365.

Is it possible to change the order of columns in a Union or to force the Union to return the Range in the Order written?
For example,

My data is in Column Order of 3, 5, 6 but I listed the Union as 5, 6, 3 because this is how I would like the output listed
But when I paste the Range to a new sheet, the output is in the original order 3, 5, 6.

Is it possible to force the output in the order I want while using Union Method?

Thanks
-w
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Not that I know of.
Although there are other ways of doing it.
 
Upvote 0
This is a VBA coding question, correct? If so, the following single line of code will copy the first 25 rows of data in Columns "E","F","C" (in that order) on Sheet1 to Sheet2 starting on cell C3 (you can of course change this as needed)...
VBA Code:
Sheets("Sheet2").Range("C3").Resize(25, 3) = Application.Index(Sheets("Sheet1").Cells,Evaluate("ROW(1:25)"),Split("5 6 3"))
 
Upvote 0
Thanks Rick,

Slicing the array seems to do the trick.
How can I make the rows part more dynamic since I don't know the row count?
Could be 10 rows this time and 100 rows next time.

Thanks,
-w
 
Upvote 0
If you Dim your variables (you should), then declare a variable named Rws as Long, then...
VBA Code:
Rws = Cells(Rows.Count, "C").End(xlUp).Row
Sheets("Sheet2").Range("C3").Resize(Rws, 3) = Application.Index(Sheets("Sheet1").Cells, Evaluate("ROW(1:" & Rws & ")"), Split("5 6 3"))
 
Upvote 0
Thanks Rick,

It's working pretty well.
It is returning the 3 columns in the desired order
However, it is also returning 3 additional columns. All 3 additional columns contain #N/A in all cells.

Additionally, it is possible to return 3 blank columns in the final output? Something like Split(5, 6, , , ,3)?

This is the code that is returning the 3 columns + 3 Columns of #N/A:
VBA Code:
 'Slice grid array
        wsTest.Range("A1").Resize(r, 6) = _
            Application.Index(wsData.Cells, _
            Evaluate("ROW(1:" & r & ")"), _
            Split("5 6 3"))

Thanks,
-w
 
Upvote 0
Thanks,

I was messing around to try to get the 3 blank columns between 6 and 3 and forgot to change it back to 3.
Any thoughts how to get the 3 blank columns between 6 and 3?

Thanks,
-w
 
Upvote 0
Untested but I think just specifying a column with no data in it will work. For example, if Column Z is empty, then change the argument to the Split to this...

Split("5 6 26 26 26 3")

And make sure the Resize uses a 6.
 
Upvote 0
Solution

Forum statistics

Threads
1,223,157
Messages
6,170,419
Members
452,325
Latest member
BlahQz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top