Not the answer you're looking for? Browse other questions tagged excel vba sorting excel-vba or ask your own question.
Sheet1.Sort.SortFields.Clear Call Sheet1.Sort.SortFields.Add(Sheet1.Columns(1), xlAscending) Call Sheet1.Sort.SetRange(Sheet1.Range('A2:C1048576')) Sheet1.Sort.Apply Line 1 clears all previous sort rules. Line 2 states that we will sorting based on column A in ascending order. Line 3 specifies the sort range. Note that in this example we only have data upto row 6, but in another example there might be data upto row 10, and so on. In order to cover all examples I specified the range to extend from row 2 all the way down to the last row (which is 1048576 in.xlsx files). Line 3 causes the sort command to execute.
Result: Example 2, Dynamic Column Indices: In example 1 we knew that we are sorting the first 3 columns based on the first column before runtime. But what if we didn’t know before hand what range we will be sorting or what column we will be sorting by? In this case you can use the function below.
The function receives as input 3 parameters:. Column2Sortby: This is the numeric index of the column that the range will be sorted.
ColumnStartSort: This is the first column in the range that will be sorted. ColumnEndSort: This is the end column in the range that will be sorted. Private Sub Example2(ByVal Column2Sortby As Integer, ByVal ColumnStart As Integer, ByVal ColumnEnd As Integer) Sheet1.Sort.SortFields.Clear Call Sheet1.Sort.SortFields.Add(Sheet1.Columns(Column2Sortby), xlAscending) Call Sheet1.Sort.SetRange(Sheet1.Range(Sheet1.Cells(2, ColumnStart), Sheet1.Cells(1048576, ColumnEnd))) Sheet1.Sort.Apply End Sub Calling context. Call Example2(1, 1, 3) The example used as the calling context above will have the same result as example 1. Example 3, Sort Multiple Columns: Lets say you want to apply a sort based on multiple columns and you don’t know the columns before running the code, you could use the function below. The function has 3 input parameters:. arrColumns: An array with the index of the range will be sorted.
ColumnStartSort: This is the first column in the range that will be sorted. ColumnEndSort: This is the end column in the range that will be sorted.
EndRow: The last row with data to sort. Column2Sortby: This is the numeric index of the column that the range will be sorted. ColumnStartSort: This is the first column in the range that will be sorted. ColumnEndSort: This is the end column in the range that will be sorted. Private Sub Example4(ByVal EndRow As long, ByVal Column2Sortby As IntegerByVal ColumnStart As Integer, ByVal ColumnEnd As Integer) Sheet1.Sort.SortFields.Clear Call Sheet1.Sort.SortFields.Add(Sheet1.Columns(Column2Sortby), xlAscending) Call Sheet1.Sort.SetRange(Sheet1.Range(Sheet1.Cells(2, ColumnStart), Sheet1.Cells(EndRow, ColumnEnd))) Sheet1.Sort.Apply End Sub As you can see above a new parameter was added to the function.
The EndRow parameter is the last row that is part of the range to sort. You can obtain the last row with a value in it using the function below: Private Function LastRow(ByVal intColumn As Integer) As Long Dim i As Long Dim flag As Boolean flag = True While flag = True If Sheet1.Cells(i, 1) <> ' Then i = i + 1 Else flag = False End If LastRow = i - 1 End Function The function receives as input the index of a column and returns the last row with data in it. The calling context would look something like this: Dim intRows As Long intRows = LastRow(1) Call Example4(intRows, 1, 1, 3) The code above will sort the range “A2:C6” based on column A. Example 5, Referring Columns by Their Character Representation In all the examples in this article the columns were reference using their numeric index. In this example I will explain how you can reference them using there character representation.
The function receives the following input parameters:. strColumn2SortBy: Character representation of the column to sort. strColumnStart: Character representation of the first column in the range to sort. strColumnEnd: Character representation of the last column in the range to sort. Private Sub Example5(ByVal strColumn2SortBy As String, ByVal strColumnStart As String, ByVal strColumnEnd As String) Sheet1.Sort.SortFields.Clear Call Sheet1.Sort.SortFields.Add(Sheet1.Columns(strColumn2SortBy & ':' & strColumn2SortBy), xlAscending) Call Sheet1.Sort.SetRange(Sheet1.Range(strColumnStart & '2:' & strColumnEnd & '1048576')) Sheet1.Sort.Apply End Sub Calling Context: Call Example5('A', 'A', 'C') The result will be the same as example 1.
Example 6: Auto Sort a sheet every 5 min. Suppose that your Excel has constantly changing data, say tickers of a stock market for example. In such cases, you would need to sort the sheet periodically. You can use the following sorting function in the module section.
The sort function below also demonstrates various parameters / options available for sorting. It is a good practice to clear the sort fields after the sorting is done.
Especially when you are going to run the function periodically. You can then call this function when the workbook is opened Private Sub WorkbookOpen Call sortSheet End Sub Example 7: Auto Sort a sheet whenever the worksheet changes. In the above example, the stock tickers change every second and hence, we cannot sort the worksheet every time a value changes. However, in some other cases, where the change is not so frequent, we can execute the code each time there is a change in that column on which the sorting is done.
Code: Sub Macro1 Columns('L:L').Select Selection.Sort Key1:=Range('L1'), Order1:=xlAscending, Header:=xlGuessOrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottomDataOption1:=xlSortNormal Columns('N:N').Select Selection.Sort Key1:=Range('N1'), Order1:=xlAscending, Header:=xlGuessOrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottomDataOption1:=xlSortNormal Columns('AL:AL').Select Selection.Sort Key1:=Range('AL1'), Order1:=xlAscending, Header:=xlGuessOrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottomDataOption1:=xlSortNormal End SubHTH Colin. This code works beautifully, except that it only sorts through row 19. Any ideas on how I can have this code automatically select the ranges? I have a similar issue, I am trying to sort my data on 3 columns (and 50 different work sheets.) and it seems to me that I am having issues selecting the range as well, I repeatedly am coming up against error 1044 at the.Apply step of my first iteration (it is a finite loop designed to sort all my work sheets for me, based on the same criteria.) here is my code, if it helps, and I would certainly welcome any feed back if anyone sees the error in my code.