Union Range Order As Written

[wsnyder]

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

 

[Fluff]

Not that I know of.
Although there are other ways of doing it.

 

[Rick Rothstein]

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)...

Sheets("Sheet2").Range("C3").Resize(25, 3) = Application.Index(Sheets("Sheet1").Cells,Evaluate("ROW(1:25)"),Split("5 6 3"))

 

[wsnyder]

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

 

[Rick Rothstein]

If you Dim your variables (you should), then declare a variable named Rws as Long, then...

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"))

 

[wsnyder]

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:

 'Slice grid array
        wsTest.Range("A1").Resize(r, 6) = _
            Application.Index(wsData.Cells, _
            Evaluate("ROW(1:" & r & ")"), _
            Split("5 6 3"))

Thanks,
-w

 

[Rick Rothstein]

The 6 in your Resize(r,6) is why 6 cells are being populated.

 

[wsnyder]

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

 

[Rick Rothstein]

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.

 

[wsnyder]

Thanks Rick,
That did the trick - awesome!
-w