VBA Sort Range

In order to sort data inside a range in VBA, you have to use the following code.

This code will give you the following result.

There are a few elements inside this code that require explanation.

Key – Here, you have to specify which column you want to sort. If you want to sort column A, you have to apply a cell inside this column, eg. A1.

Order – You can specify here whether you want to sort values in an ascending or descending order. We sort in ascending order by providing order1:=xlAscending.

Header – We can specify here whether our range has headers. Because our data has only values, we specified that we don’t use header – Header:=xlNo.

Sort range with a header

In the earlier example, we specified that we don’t use the header. Let’s take a look at our example, this time with a header.

If we sort it in ascending order we are going to get something like this.

This is definitely not what we want. But we can do a slight modification to our code and specify that we want to use the header.

Now, we get what we wanted – the table is sorted and the header is in its place. You can also sort the data from the largest to the lowest value by using the following code.

This is the result.

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