Rearrange the excel columns by the columns header name

[sonypcg]

How could I rearrange the columns by the columns header name.
I need to process a report on weekly basis,the raw worksheet has 23 columns.I abstract a part of them as example.
Raw columns sequence：

[TABLE="width: 500"]
<tbody>[TR]
[TD]QTY[/TD]
[TD]Payment Terms[/TD]
[TD]Contract No.[/TD]
[/TR]
</tbody>[/TABLE]



Desired columns sequence:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Contract No.[/TD]
[TD]Payment terms[/TD]
[TD]QTY[/TD]
[/TR]
</tbody>[/TABLE]


Any idea how to automatize the columns rearrangement with VBA code?
Many thanks in advance.

 

[mumps]

Re: How to rearrange the excel columns by the columns header name

Try:

Sub SortCols()
    Dim lColumn As Long
    lColumn = Cells(1, Columns.Count).End(xlToLeft).Column
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range(Cells(1, 1), Cells(LastRow, lColumn)).Sort Key1:=Range(Cells(1, 1), Cells(1, lColumn)), Order1:=xlAscending, Orientation:=xlLeftToRight
End Sub

 

[MickG]

Re: How to rearrange the excel columns by the columns header name

Try this for results on sheet2.

[COLOR="Navy"]Sub[/COLOR] MG02Oct06
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant
Ray = Cells(1).CurrentRegion.Resize(, 23)
ReDim nRay(1 To UBound(Ray, 1), 1 To UBound(Ray, 2))
                                                                       '[COLOR="Green"][B]>>Change the columns array Here to what you want<<< At the Moment its columns 10 TO 23 then 1 TO 9[/B][/COLOR]
nRay = Application.Index(Ray, Evaluate("ROW(1:" & UBound(Ray, 1) & ")"), Array(10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 1, 2, 3, 4, 5, 6, 7, 8, 9))
Sheets("Sheet2").Range("A1").Resize(UBound(Ray, 1), UBound(Ray, 2)).Value = nRay
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Jonmo1]

Re: How to rearrange the excel columns by the columns header name

Without code you can sort columns just like you can rows.

After you click the Sort Button, and you have your choices of sort by column..
Click the Options button and choose "Sort Left to right"
Then you can sort by the top row.

 

[sonypcg]

Re: How to rearrange the excel columns by the columns header name

Hi Mumpls
Many thanks for your prompt help.The code seems to rearrange them in alphabetical order?
Sry may be I misunderstood,could pls give me further instruction?

 

[mumps]

Re: How to rearrange the excel columns by the columns header name

I'm not sure I understand. What further instruction would you like?

 

[sonypcg]

Re: How to rearrange the excel columns by the columns header name

Actually the whole picture would be more complicated than my abstracted columns example,which I am unable to sort it out in alphabetical order with build in sort function
[TABLE="width: 1280"]
<tbody>[TR]
[TD="width: 64"]Fulfillment Center[/TD]
[TD="width: 64"]Region[/TD]
[TD="width: 64"]Country[/TD]
[TD="width: 64"]Office[/TD]
[TD="width: 64"]Orig Contract No.[/TD]
[TD="width: 64"]Order No[/TD]
[TD="width: 64"]BSA Line NO.[/TD]
[TD="width: 64"]Delivery Detail[/TD]
[TD="width: 64"]Delivery Id[/TD]
[TD="width: 64"]Order Line No.[/TD]
[TD="width: 64"]Apply Line[/TD]
[TD="width: 64"]Order Qty[/TD]
[TD="width: 64"]Qty[/TD]
[TD="width: 64"]Unit price[/TD]
[TD="width: 64"]Amount[/TD]
[TD="width: 64"]Currency[/TD]
[TD="width: 64"]Amount USD[/TD]
[TD="width: 64"]Delivery Batch[/TD]
[TD="width: 64"]Product Code[/TD]
[TD="width: 64"]Item[/TD]
[/TR]
</tbody>[/TABLE]

 

[sonypcg]

Re: How to rearrange the excel columns by the columns header name

Many thanks Mick
Actually I took advantage of the column array in my original code to rearrange columns,however the disadvantage of it is not dynamic.The challenge is that sometime the columns array between raw worksheets varies,but the column headers name are always identical.

 

[mumps]

Re: How to rearrange the excel columns by the columns header name

The macro I suggested works with the headers you posted in Post #7 and will work any number of columns.

 

[sonypcg]

Re: How to rearrange the excel columns by the columns header name

The application of your code together with the abstracted example would be highly appreciated. Sry I can not fully understand the code.
The raw worksheet contain at least 23 columns,some raw files are with even more columns. The columns array is different,only the desired column header name are the same.


Sub SortCols()
Dim lColumn As Long
lColumn = Cells(1, Columns.Count).End(xlToLeft).Column
Dim LastRow As Long
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range(Cells(1, 1), Cells(LastRow, lColumn)).Sort Key1:=Range(Cells(1, 1), Cells(1, lColumn)), Order1:=xlAscending, Orientation:=xlLeftToRight
End Sub[TABLE="width: 448"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 448"]
<tbody>[TR]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]