VBA sort columns in table

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.

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.

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.

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.