VBA sort range

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

Sub Sort()
    Range("A1:A10").Sort key1:=Range("A1"), order1:=xlAscending, Header:=xlNo
End Sub

This code will give you the following result.

There are 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 an 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.

Sub SortWithHeader()
    Range("A1:A10").Sort key1:=Range("A1"), order1:=xlAscending, Header:=xlYes
End Sub

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.

Sub SortWithHeaderDescending()
    Range("A1:A10").Sort key1:=Range("A1"), order1:=xlDescending, Header:=xlYes
End Sub

This is the result.

Posted in vba