Excel VBA Sort Range

As part of data organizing or data structuring, it is important to sort the data and make it organized. A similar thing is also available with VBA. A common question about new learners of VBA is how we can use this sort of option as part of VBA automation. This article guides you through the VBA Sort range in detail.

With Excel, we are all familiar with the sort option available under the DATA tab.

Sort Option in VBA

To use the sort option, first, we need to decide what our data range is and mention the same data range by using the RANGE object in VBARANGE Object In VBARange is a property in VBA that helps specify a particular cell, a range of cells, a row, a column, or a three-dimensional range. In the context of the Excel worksheet, the VBA range object includes a single cell or multiple cells spread across various rows and columns.read more. Then, only we can access the “Sort” option in VBA. For example, assuming my data range is from A1 to D10, we can provide the data range as follows.

Code:

Sub Sort_Range_Example()

Range (“A1:D10”)

End Sub

Now, put a dot and select the “SORT” method.

Sub Sort_Range_Example()

Range(“A1:D10”).Sort

End Sub

Below is the syntax of the SORT method of range. Though syntax has different arguments, we do not need all of them for our VBA codingVBA CodingVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more. So, we need only a few elements.

[Key1]: In the data range we are sorting, we need to specify which column we need to sort. For example, in the data range of A1: D10, if we want to sort the data based on column B, then [Key1] will be Range(“B1”).

[Order1]: The mentioned column in the [Key1] argument should sort in what order? We can choose two options here: “xlAscending” or “xlDescending.”

Header: The mentioned data range has headers or not? If yes, we can supply “xlYes,” or else we can supply “xlNo.”

Example of Sort Range in VBA

Let us take the example of the Excel VBA sort range to understand this better.

Look at the below data structure.

We have data from A1 to E17. First, we will sort the data based on “Country-wise.” Then, follow the below steps to write the code to sort the data.

Step 1: Start the excel macroExcel MacroA macro in excel is a series of instructions in the form of code that helps automate manual tasks, thereby saving time. Excel executes those instructions in a step-by-step manner on the given data. For example, it can be used to automate repetitive tasks such as summation, cell formatting, information copying, etc. thereby rapidly replacing repetitious operations with a few clicks. read more procedure.

Sub Sort_Range_Example()

End Sub

Step 2: First, mention the data range using the RANGE object.

Sub Sort_Range_Example()

Range (“A1:E17”)

End Sub

Step 3: Now, choose the “Sort” method of the Range object.

Sub Sort_Range_Example()

Range(“A1:E17”).Sort

End Sub

Step 4: Since we are sorting the data based on “Country-wise,” our Key 1 argument column will be Range (“B1”).

Sub Sort_Range_Example()

Range(“A1:E17”).Sort Key1:=Range(“B1”),

End Sub

Step 5: Once we mention the required column, we need to mention in what order we need to sort the data, and “Order1” will be the “xlAscending” order.

Sub Sort_Range_Example()

Range(“A1:E17”).Sort Key1:=Range(“B1”), Order1:=xlAscending,

End Sub

Step 6: Our data has headers so that that header will be “xlYes.”

Sub Sort_Range_Example()

Range(“A1:E17”).Sort Key1:=Range(“B1”), Order1:=xlAscending, Header:=xlYes

End Sub

We have mentioned all the elements which are required to sort the data. Then, execute the code by pressing the F5 function and key and see the result.

Output:

The data has been sorted based on country names from A to Z.

Now, assume we need to sort the data based on country-wise. Also, we need to sort the “Gross Sales” from highest to lowest. In such cases, we also need to use Key2 and Order2 arguments.

After mentioning Key1 and Order1, let us choose Key2.

Sub Sort_Range_Example()

Range(“A1:E17”).Sort Key1:=Range(“B1”), Order1:=xlAscending,Key2:=

End Sub

Since we are sorting the data based on the “Gross Sales” column, our Key2 column name will be Range(“D1”).

Sub Sort_Range_Example()

Range(“A1:E17”).Sort Key1:=Range(“B1”), Order1:=xlAscending,Key2:=Range(“D1”),

End Sub

Once the Key2 is specified, we need to decide on the sorting pattern of whether it is ascending order or descending order in the Order2 argument. Since we are sorting the sales value from largest to smallest, we can choose the “xlDescending” order.

Sub Sort_Range_Example()

Range(“A1:E17”).Sort Key1:=Range(“B1”), Order1:=xlAscending, Key2:=Range(“D1”), Order2:=xlDescending, Header:=xlYes

End Sub

After that, mention the header argument as “xlYes.” Now, run the code and see what happens.

Previously, it was sort based only on “Country-wise.” But this time, it sorted based on “Country-wise” first and then on “Gross Sales” from highest to lowest.

Like this, we can use the “Sort” method in VBA to organize the data.

Things to Remember about Excel VBA Sort Range

  • The sort is a method available in VBA. To access this method, we need to specify the range of cells we will sort.If the data range includes headers, we need to choose the header option as “xlYes.” If not, we can choose “xlNo.”

This article is a guide to VBA Sort Range. Here, we discuss how to sort range using the specific column in VBA, Excel example, and downloadable Excel templates. You may also look at these useful functions in Excel: –

  • Pivot Table SortVBA Variable RangeNamed Range in VBAUsedRange in VBA