Rearrange columns based on number at top

[Nick70]

Hi,

I have a set of data in range(B1:E20).
In row (B1:E1) there are numbers 1 to 4 in this order 2,1,4,3.
I would like to rearrange the columns so that column B is the one which has number 1 then column C has number 2 then column D has number 3 and column E has number 4.
Basically the columns have to be 'sorted' in ascending order based on their numbers in row 1.

How do I do that?

Thanks,
Nic

 

[Sanjeev1976]

You can try :

=SORTBY(B1:E20,B1:E1)

 

[Nick70]

That worked fine thanks.
It creates a new set of data with correct order.

If there is a VBA code to actually move position of existing set of columns this would also be helpful.

Thanks,
Nic

 

[Sanjeev1976]

VBA code as per your request :

Sub SortByColumnNo()
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("B1:E20")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[awoohaw]

Have you tried the data sort "options" of "Left to Right":

 

[Nick70]

Great thanks guys!
N.