Sort a Range in Excel VBA

You can use the Sort method in Excel VBA to sort ranges in Excel. This article provides clear and practical examples to help you get started with sorting ranges using Excel VBA.

Sort a Single Column

To sort a single column range, place a command button on your worksheet and add the following code line:

Result when you click the command button on the sheet:

Explanation: Key1:=Range("A1") specifies the primary key to sort by, which is the first cell in the range. Order1:=xlAscending sorts the data in ascending order. Header:=xlNo indicates that the range does not have a header row.

Sort a Range with Two Columns

Now, let's use the Sort method in Excel VBA to sort a range with two columns (see picture below). The Sort method works on the range A1:B8 this time.

To sort by the Score column in descending order, use the following code line:

Result when you click the command button on the sheet:

Explanation: Key1:=Range("B2") specifies the primary key to sort by, which is the first data cell in the Score column. We use Range("B2") because our data has headers, and the actual sorting starts from the second row. Order1:=xlDescending sorts the data in descending order. Header:=xlYes indicates that the range has a header row.

Sort by Multiple Columns

Now, let's use the Sort method in Excel VBA to sort by multiple columns (see picture below). The Sort method works on the range A1:C8 this time.

To sort by Class in ascending order and then by Score in descending order, use the following code line:

Result when you click the command button on the sheet:

Explanation: this code uses two keys: the first key (Key1) sorts by Class in ascending order, and the second key (Key2) sorts by Score in descending order.

Dynamic Sorting

For dynamic sorting, where the number of rows frequently changes, use the CurrentRegion property to include new data automatically.

1. Result when you click the command button on the sheet:

2. Now, add a new row.

3. Sort again using the same VBA code line.

4. If your data contains empty rows, the CurrentRegion method may not work as expected. In such cases, use Cells.Sort to sort your data.

Note: this code line sorts the entire sheet based on the specified column, even when there are empty rows. Ensure there is no data below the range to be sorted when using this code line!