Microsoft Excel - Macros - Sort - Sort

An example showing how to sort a worksheet.

' 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

---

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