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 the 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

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 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

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 the 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 a similar code as before, only the first column will be sorted. Let’s modify our example to add an additional range, which is the 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

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.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Post Views: 32,633