Vba sort code

[bflan0524]

if i had a sheet called 'Results By Customer' and column AK was called % Change. i wanted to build a macro then when you pressed the form it would filter that column by the top 10 customers by that criteria, i would also do another one for the bottom ten

ty

 

[pbornemeier]

This code should be placed in a standard module in the workbook that contains the target worksheet.
Add a form with 2 buttons that call each of the first 2 subs or put those buttons on a worksheet.

Option Explicit

Sub ShowAKTopTen()
    SortAK
    Worksheets("Results By Customer").Range("A1").CurrentRegion.AutoFilter Field:=37, Criteria1:="10", _
        Operator:=xlTop10Items
End Sub

Sub ShowAKBottomTen()
    SortAK
    Worksheets("Results By Customer").Range("A1").CurrentRegion.AutoFilter Field:=37, Criteria1:="10", _
        Operator:=xlBottom10Items
End Sub

Sub SortAK()

    With Worksheets("Results By Customer")
    
        .AutoFilterMode = False
        .Range("A1").CurrentRegion.AutoFilter
        .AutoFilter.Sort.SortFields.Clear
        .AutoFilter.Sort.SortFields. _
            Add Key:=Intersect(.UsedRange, .Range("AK:AK")), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortTextAsNumbers
        With .AutoFilter.Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
End Sub