Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Microsoft Excel - Macros - Sort - Sort ======

An example showing how to sort a worksheet.

<code excel>
' Sorts all queries on all sheets.
' Unfortunately, the SQL sort does not match how Excel sorts the data, and therefore these are resorted per Excel.
Sub Sort_Queries()

    Dim lastrow_Test1 As Long
    Dim lastrow_Test2 As Long

    
    
    ' Ask user.
'    If ctrl_ask_before_running_subroutine = True Then
'        If MsgBox("Sort all queries?", vbYesNo) = vbNo Then Exit Sub
'    End If


    ' Update StatusBar.
    Application.StatusBar = "Sorting Queries..."


    With Workbooks(wb_name)
    
        With .Sheets("Test1")
    
            ' Activates the sheet.
            .Activate
    
            
            ' Update StatusBar.
            Application.StatusBar = "Sorting Queries...on Test1"
    
    
            ' Determine the number of rows.
            lastrow_Test1 = .Cells(Rows.Count, 1).End(xlUp).Row
    

            ' Do the sort.
            With .Sort
            
                '.AutoFilter
                With .SortFields
                    .Clear
                    .Add Key:=Range("A1:A" & lastrow_Test1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                End With
                
                .SetRange Range("A1:B" & lastrow_Test1)
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
                
            End With


            ' Select A1.
            ScrollTo ActiveSheet.name, "A1"

        End With
        
        
        With .Sheets("Test2")
    
            ' Activates the sheet.
            .Activate
    
    
            ' Update StatusBar.
            Application.StatusBar = "Sorting Queries...on Test2"
    
    
            ' Determine the number of rows.
            lastrow_Test2 = .Cells(Rows.Count, 1).End(xlUp).Row
    

            ' Do the sort.
            With .Sort
            
                '.AutoFilter
                With .SortFields
                    .Clear
                    .Add Key:=Range("A1:A" & lastrow_Test2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                End With
                
                .SetRange Range("A1:N" & lastrow_Test2)
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
                
            End With


            ' Select A1.
            ScrollTo ActiveSheet.name, "A1"

        End With
        
    End With
End Sub

</code>

----

<code>
Sub b_SortData()
  Cells.Select
  ActiveWorkbook.Worksheets("Cleaned Data").Sort.SortFields.Clear
  ActiveWorkbook.Worksheets("Cleaned Data").Sort.SortFields.Add2 Key:=range( _
      "B2:B1908"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
      xlSortNormal
  ActiveWorkbook.Worksheets("Cleaned Data").Sort.SortFields.Add2 Key:=range( _
      "D2:D1908"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
      xlSortNormal
  With ActiveWorkbook.Worksheets("Cleaned Data").Sort
      .SetRange range("A1:AF1908")
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
  End With
End Sub
</code>