In order to sort data inside a range in VBA, you have to use the following code.
|
1 2 3 |
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 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.
|
1 2 3 |
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.
|
1 2 3 |
Sub SortWithHeaderDescending() Range("A1:A10").Sort key1:=Range("A1"), order1:=xlDescending, Header:=xlYes End Sub |
This is the result.
