vba compare and order in new row

[Seeker2013]

i have a header, let say number 1-20
A1-B1-C1-D1-E1-F1-G1-H1-I1-J1-K1-L1-M1-N1-O1-P1-Q1-R1-S1-T1
1--2--3--4--5--6--7--8--9-10-11-12-13-14-15-16-17-18-19-20 (this is the header)
14-16-25-22-21-13-14-20-07-05-13-29-10-12-07-11-08-15-02-06 (this is 2nd row, represent the frequency)
questions:
how can i use the vba to compare the frequency and place on row 3 from biggest figure to smallest figure,
and on row 4 from smallest figure to biggest figures?



thank you very much!

 

[kieran]

VBA is not necessary,
in cell A3 paste =SMALL($A$2:$T$2,COLUMN())and copy across to row T
in cell A3 paste =LARGE($A$2:$T$2,COLUMN())and copy across to row T

 

[alansidman]

As Kieran indicated, VBA is not necessary, but here is a VBA solution anyway. But Kieran's solution appears much simpler.

Option Explicit


Sub as11()


    Range("A2:T2").Copy Range("A3")
    Application.CutCopyMode = False
    Range("A3:T3").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A3:T3"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A3:T3")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A3:T3").Copy Range("A4")
    Application.CutCopyMode = False
    Range("A4:T4").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A4:T4"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A4:T4")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub