sort data | General Excel Questions & Answers | Excel Forum

Hi,

I'm using the following, for a fixed number of columns (but you can also add in a variable for the last column). I've got a Public variable 'FinalRow' where I define the number of Rows as this will vary, ( FinalRow = Cells(Rows.Count, 5).End(xlUp).Row) using Column E (Column 5) for this, and call the sort routine from another sub.

The macro sorts on Columns E and G, so you would need to set these according to your needs. It also preserves the header at the top.

Hope this helps.

Tony.

Sub PatResultsSort()
'
' PatResultsSort Macro
' Sort PAT Results
'

'
    Range("A1:O" & FinalRow).Select
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add Key:=Range _
        ("E2:E" & FinalRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add Key:=Range _
        ("G2:G731"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub