Differences

This shows you the differences between two versions of the page.

--- microsoft_excel:macros:sort:sort [2021/08/04 14:47] – created peter
+++ microsoft_excel:macros:sort:sort [2021/08/04 14:47] (current) – peter
@@ Line 1: / Line 1: @@
 ====== 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>