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.

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.

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.

Now, if you run the code you will get the following outcome.