Differences

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

--- microsoft_excel:macro_sort [2016/07/11 11:32] – created 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>