Carpe PM

Say you want to sort in Excel by columns W, V, S, M & L in a spreadsheet and the actual data starts at row 6 from columns A to Z. This script sorts any number of rows by those columns starting at row 6 in the Excel spreadsheet. Sorting in Excel with macros is not always well documented. Hopefully, this post will pop up in search results in the future to help others with their macro sorting woes.

Sub derpurtSortColumns()
'
' derpurtSortColumns Macro
'
    Dim mySheet As Worksheet
    Dim myRange As Range
    Dim LastRow As Long

    Set mySheet = ActiveWorkbook.Worksheets(1)

    mySheet.Activate

    'Determine Last Row
    LastRow = ActiveSheet.UsedRange.Rows.Count

    mySheet.Sort.SortFields.Clear

    'Sort Data
    mySheet.Sort.SortFields.Add Key:=Range("W6"), _
        SortOn:=xlSortOnValues, _
        Order:=xlAscending, _
        DataOption:=xlSortNormal
    mySheet.Sort.SortFields.Add Key:=Range("V6"), _
        SortOn:=xlSortOnValues, _
        Order:=xlAscending, _
        DataOption:=xlSortNormal
    mySheet.Sort.SortFields.Add Key:=Range("S6"), _
        SortOn:=xlSortOnValues, _
        Order:=xlAscending, _
        DataOption:=xlSortNormal
    mySheet.Sort.SortFields.Add Key:=Range("M6"), _
        SortOn:=xlSortOnValues, _
        Order:=xlAscending, _
        DataOption:=xlSortNormal
    mySheet.Sort.SortFields.Add Key:=Range("L6") _
        SortOn:=xlSortOnValues, _
        Order:=xlAscending, _
        DataOption:=xlSortNormal

    With mySheet.Sort
        .SetRange Range("A6:Z" & LastRow)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    Range("A1").Select
End Sub

This entry was posted on Wednesday, November 25th, 2009 at 5:15 pm and is filed under Programming. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.