VBA Sort Range

Excel VBA Sort Range

Sorting a range in VBA is done by range.sort method, it is a property of the range method with which a user can sort a range in order, the arguments for this function are the Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, OrderCustom, MatchCase, Orientation, SortMethod, DataOption1, DataOption2, DataOption3, all the arguments for this function are optional.

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

With excel, we all are familiar with the option of the sort which is available under the DATA tab.

VBA Sort Range Example 1

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 VBA, then only we can access the “Sort” option in VBA. For example, assume my data range is from A1 to D10, then we can provide the data range as follows.

Code:

Sub Sort_Range_Example()

  Range ("A1:D10")

End Sub
VBA Sort Range Example 1.1

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

Code:

Sub Sort_Range_Example()

  Range("A1:D10").Sort

End Sub
 Example 1.2

Below is the syntax of the SORT method of range. Though syntax has different arguments, we don’t 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.

VBA Sort Range Example 1.3

[Key1]: In the data range that 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 be 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’s take the example of excel VBA sort range to understand this in a better manner.

You can download this VBA Sort Range Excel Template here – VBA Sort Range Excel Template

For example, look at the below data structure.

 Example 1.4

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

Step 1: Start the excel macro procedure.

Code:

Sub Sort_Range_Example()

End Sub
VBA Sort Range Example 1.5

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

Code:

Sub Sort_Range_Example()

  Range ("A1:E17")

End Sub
 Example 1.6

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

Code:

Sub Sort_Range_Example()

  Range("A1:E17").Sort

End Sub
VBA Sort Range Example 1.7

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

Code:

Sub Sort_Range_Example()

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

End Sub
 Example 1.8

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

Code:

Sub Sort_Range_Example()

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

End Sub
VBA Sort Range Example 1.9

Step 6: Our data has headers, so Header will be “xlYes.”

Code:

Sub Sort_Range_Example()

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

End Sub
Example 1.10

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

Output:

VBA Sort Range Example 1.11

Data has been sorted based on Country names in the order of 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 need to make use of Key2 and Order2 arguments as well.

After mentioning Key1 & Order1, let’s choose Key2.

Code:

Sub Sort_Range_Example()

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

End Sub
Example 1.12.0

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

Code:

Sub Sort_Range_Example()

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

End Sub
VBA Sort Range Example 1.13

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

Code:

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.

Output:

 Example 1.14

Previously it has sorted based only on “Country-wise” but this time it has sorted based on “Country-wise” first and then also 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, and to access this method, we need to specify what the range of cells we are going to sort.
  • If the data range includes headers, then we need to choose the header option as “xlYes”, if not, we can choose “xlNo.”

This has been a guide to VBA Sort Range. Here we discuss how to sort range using the specific column in VBA along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Reader Interactions

Comments

  1. AvatarTAM M HUYNH says

    Thanks.
    Learn a lot from this way of explaining how to write VBA code.