In order to sort columns in a table with VBA, you have to create one.
This is the table we are going to use.
After you created the table, go to Design >> Properties and change the name of the table.
Sort table ascending and descending using ListObject
The following code is going to sort the table from the lowest to the highest value.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Sub Sort() Dim ws As Worksheet Dim tbl As ListObject Dim rng As Range Set ws = ActiveSheet Set tbl = ws.ListObjects("myTable") Set rng = Range("myTable[Numbers]") With tbl.Sort .SortFields.Clear .SortFields.Add Key:=rng, SortOn:=xlSortOnValues, Order:=xlAscending .Header = xlYes .Apply End With End Sub |
Code explanation
2-4. Three variables are declared.
The worksheet variable is set to the sheet we are currently in. Next, we are using ListObject. This object contains all the list objects on a worksheet. In this case, the tbl variable gets all objects associated with the myTable table.
The last variable is the range of the table with the header name.
.SortFields.Clear – is necessary because otherwise the table won’t be sorted.
Order:=xlAscending – The sort order is set on ascending (lowest to highest).
.Header = xlYes – The header we use.
If you want to use the shortest code possible, you can use this one.
|
1 2 3 4 5 |
Sub Sort() ActiveSheet.ListObjects("myTable").Sort.SortFields.Clear ActiveSheet.ListObjects("myTable").Sort.SortFields.Add Key:=Range("myTable"), Order:=xlDescending ActiveSheet.ListObjects("myTable").Sort.Apply End Sub |
Sort table by two and more columns
So far, we used a table with a single header. Now, let’s try something more complicated.
If we use the similar code as before, only the first column will be sorted. Let’s modify our example to add additional range, which is column Last Name.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Sub Sort() Dim ws As Worksheet Dim tbl As ListObject Dim rng As Range Set ws = ActiveSheet Set tbl = ws.ListObjects("myTable") Set rng1 = Range("myTable[First Name]") Set rng2 = Range("myTable[Last Name]") With tbl.Sort .SortFields.Clear .SortFields.Add Key:=rng1, Order:=xlAscending .SortFields.Add Key:=rng2, Order:=xlAscending .Header = xlYes .Apply End With End Sub |
Now, if you run the code you will get the following outcome.
