How to Sort Data Range by Specific Column in Excel VBA

1. Sort Data Range by Specific Column in VBA
2. Sort Data Range by Specific Column Using the Range.Sort() Method in VBA
3. Sort Data Range by Specific Column by Recording a Macro in VBA
4. Sort Data Range by Specific Column Using Single-Level Sorting in VBA
5. Sort Data Range by Specific Column Using Multiple-Level Sorting in VBA
6. Sort Data Range by Specific Column Using Different Parameters in VBA

We will introduce different methods to sort a data range by specific column in excel VBA.

Sort Data Range by Specific Column in VBA

Excel has built-in features to sort the data available in ribbon and the sort dialog box. Yet we may need to use VBA to sort data whenever sorting data is required in coding.

We can save a lot of time and energy by creating a macro for the sorting process of a given data. Macro allows us to do the whole process with a single click.

Data sorting can be done using multiple techniques, and we can choose a method suitable for our data. We will discuss these methods one by one in the following sections.

Sort Data Range by Specific Column Using the Range.Sort() Method in VBA

The Range.Sort() method can quickly sort data in VBA. However, some additional parameters should be defined while sorting the data through VBA.

These parameters include Key, Order, and Header.

1. Key - specifies the column we want to sort.
2. Order - specifies the order type: descending or ascending.
3. Header - tells VBA whether the Header data is included in the sorting process or not.

A code with range B1 to B13, using column B1, ascending order, and a header for data using VBA code is shown below.

Example:

# VBA
Sub usingRangeSort()
Range("B1:B13").Sort Key1:=Range("B1"), _
    Order1:=xlAscending, _
    Header:=xlYes
End Sub

Output:

As you can see from the example, we can sort a range using the Range.Sort() method.

Sort Data Range by Specific Column by Recording a Macro in VBA

Recording a macro for VBA sorting can be complicated since it needs to involve all the parameters in the data sorting, yet it can be helpful since it shows us how the code will do the work. This method applied on an excel worksheet may look as shown below.

Example:

# vba
Sub savedMacro()
Range("A1:B13").Sort Key1:=Range("A1"), _
    Order1:=xlAscending, _
    Header:=xlYes
End Sub

Output:

As you can see from this example, we can save the macro and use it in different sheets whenever we want it. A code can easily undergo a copy-paste process being applied on multiple excel worksheets and hence can hasten up the process of bulk data through VBA coding.

Sort Data Range by Specific Column Using Single-Level Sorting in VBA

This process is super easy as it does not need to include the parameters one by one but uses the default parameters independently. A user must set the parameters of all the worksheets they involve by default.

A sample of the method is shown below.

# vba
Sub SortWithSingleLevel()

Worksheets("Sheet1").Sort.SortFields.Clear

Range("A1:B13").Sort Key1:=Range("A1"), Header:=xlYes
End Sub

Output:

As you can see from the above example, we can sort the sheet data using the range with single-level sorting. Now, let’s imagine if we want to do the same thing for multiple-level sorting.

Sort Data Range by Specific Column Using Multiple-Level Sorting in VBA

We can involve more than one level while sorting the data through the VBA code.

For example, if we are to run a code that sorts Key1 first and then Key2. We involve the two orders, Order1 and Order2, i.e., Order1 is associated with Key1 and Order2 is associated with Key2.

The code works as shown below.

# vba
Sub SortWithMultiLevel()

Worksheets("Sheet1").Sort.SortFields.Clear

Range("A1:B13").Sort Key1:=Range("A1"), Key2:=Range("B1"), Header:=xlYes, _
Order1:=xlAscending, Order2:=xlDescending

End Sub

Output:

As you can see from the above example, we can sort our range with multiple-level sorting by using multiple keys and orders.

Sort Data Range by Specific Column Using Different Parameters in VBA

Now, we will discuss different parameters that can be used in sorting in VBA.

Sort Data With the SortOn Parameter in VBA

This defines the parameter on which we want the sorting process of the date to be done. The parameter can be cell values, font color, or cell (background) color.

The default setting is of the cell value, and it goes as shown below.

# vba
SortOn:= xlSortOnValues

Sort Data With the Order Parameter in VBA

Order can be chosen as ascending or descending orders (as discussed in the earlier article). However, the default setting is of ascending order, and the syntax is as shown below.

# vba
Order:= xlAscending

Sort Data With the DataOption Parameter in VBA

This parameter defines how a VBA code sorts numeric and text data. We can sort numbers and texts separately using this parameter. The syntax that is used is as shown below.

# vba
DataOption:= xlSortNormal

Sort Data With the Header Parameter in VBA

It describes whether the header of the data is to include in the sorting process or not. The syntax of header sorting is shown below.

# vba
Header:= xlYes

If there is no header and we want to start sorting from the start, we can use the following code below.

# vba
Header:= xlNo

Sort Data With the MatchCase Parameter in VBA

MatchCase sorting defines whether the process is case-sensitive or not. The syntax of MatchCase sorting is shown below.

# vba
MatchCase:= False

Sort Data With the Orientation Parameter in VBA

The parameter defines the orientation of sorting from top to down or through the columns. The default syntax is shown below.

# vba
Orientation:= xlTopToBottom