Differences

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

--- microsoft_excel:macro_sort [2020/11/15 21:10] – peter
+++ microsoft_excel:macro_sort [2021/08/04 14:26] (current) – removed peter
@@ Line 1: / Line 1: @@
-====== Microsoft Excel - Macro 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>