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

Range(“A1:A8”).Sort Key1:=Range(“A1”) Order1:=xlAscending Header:=xlNo

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.

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:

Range(“A1:B8”).Sort Key1:=Range(“B2”) Order1:=xlDescending Header:=xlYes

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.

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:

Range(“A1:C8”).Sort Key1:=Range(“B2”) Order1:=xlAscending Key2:=Range(“C2”) Order2:=xlDescending Header:=xlYes

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.

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

Range(“A1”).CurrentRegion.Sort Key1:=Range(“B2”) Order1:=xlDescending Header:=xlYes

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.

Cells.Sort Key1:=Columns(“B”) Order1:=xlDescending Header:=xlYes

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!