CODEWITHSUNDEEP
vbaexcel
user1884324
user1884324

Reputation: 703

alphabetically re-order specific range of columns in a worksheet

I've a excel worksheet with the title for each column is on the row 3.
I would like to know (via excel vba) how to re-arrange the columns (column D - Z) in alphabetical order base on the title name.
Thanks for guidance.

eg. before arrange 

. . . . . column D | column E | column F | column G | ...  
row 1  
row 2  
row 3  zebra | car |  monkey | balloon | ...

eg. after re-arrange 

. . . . . column D | column E | column F | column G | ...  
row 1  
row 2  
row 3  balloon | car |  monkey | zebra | ...

Upvotes: 1

Views: 1275

Answers (1)

MikeD
MikeD

Reputation: 8941

You need any Sort algorithm and apply it to columns (instead of rows)

Here's a quick & dirty one (ok it's not a super fast sorter, just out of my memory, but ...):

Sub HorSort(SortRange As Range, SortRow As Long)
Dim Idx As Long, Jdx As Long, Kdx As Long, Tmp As Variant

    For Idx = 1 To (SortRange.Columns.Count - 1)
        For Jdx = 1 To (SortRange.Columns.Count - 1)

        ' compare values in row to be sorted
            If SortRange(SortRow, Jdx) > SortRange(SortRow, Jdx + 1) Then

            ' swap all cells in column with the one to the right
                For Kdx = 1 To SortRange.Rows.Count
                    Tmp = SortRange(Kdx, Jdx)
                    SortRange(Kdx, Jdx) = SortRange(Kdx, Jdx + 1)
                    SortRange(Kdx, Jdx + 1) = Tmp
                Next Kdx
            End If
        Next Jdx
    Next Idx
End Sub

Sub Test()
    HorSort Selection, 1
End Sub

Enter following data at A1

5 2 4 1 3
A D B E C
1 2 3 4 5

select A1..E3 and run each of

HorSort Selection, 1
HorSort Selection, 2
HorSort Selection, 3

from Sub Test(). You are of course not limited to 5 columns.

Upvotes: 1

Related Questions