excel - Delete an array column, and change position of two columns

Question

So let's say i have and 4x4 array of various numbers.

I want to delete the third array column, and switch positions of the second and fourth columns within the array.

The ultimate goal is copying information from a sheet into an array, and prepping the array to paste into another sheet.

How would I be able to do this?

Sub test()
Dim Arr as variant
Arr=Sheets("Worksheet").Range("A1:D4")
'Delete third column
'Switch second and what was the fourth but now is the 3rd column.
Sheets("Sheet2").Range("A1").Resize(UBound(Arr, 1), UBound(Arr, 4)) = Arr
End Sub

Answer 1

Alternative via Application.Index() function

For the sake of the art an approach without loops allowing to get any new column order defined just by listing the new column positions via

     Array(1, 4, 2)

in other words

• the 1st column,
• (the 3rd one omitted=deleted),
• the remaining columns 4 and 2 in switched order*.

Btw it would even be possible to repeat columns, just insert its number at any position in the column array, e.g. to repeat a date column with changed formatting (assuming date in column 4 e.g. via Array(1, 4, 4, 2)

Sub DeleteAndSwitch()
'[1]get data
    Dim data: data = Sheet1.Range("A1:D4")
'[2]reorder columns via Array(1, 4, 2), i.e. get 1st column, 4th and 2nd column omitting the 3rd one
'   (evaluation gets all existing rows as vertical 2-dim array)
    data = Application.Index(data, Evaluate("row(1:" & UBound(data) & ")"), Array(1, 4, 2))
'[3]write to any target
    Sheet2.Range("A1").Resize(UBound(data), UBound(data, 2)) = data
End Sub

Related link

See Some peculiarities of the the Application.Index() function