VBA Sort Range

What is Excel VBA Sort Range?

VBA Sort Range is used to sort a single key (column) or multiple keys (multiple columns) using the “SORT” method. It is a property of the Range object in VBA. We can sort the range of columns either in ascending or descending order.

For example, let us look at an example of sorting a column range in the ascending order of the alphabet. Suppose the data in Excel is as shown below:

To sort the above data in ascending order, write the code below.

Run the code by clicking the Run option in the VB ribbon or F5 directly to see the result below:

Code:

Sub VBASort_Ex()
Range(“A1”, Range(“A1”).End(xlDown)).Sort key1:=Range(“A1”), Order1:=xlAscending
End Sub

Sort Option in VBA

The Sort option in VBA, as mentioned above, is used to sort a range of cells using the Range object in VBA. For example, consider a data range in Excel workbook from A1 to C1. We can use the code in VBA as shown below:

Code:

Sub SortOption_VBA_Ex()
Range(“A1:C1”)
End Sub

    Follow the range with a dot. It will show the properties of Range. Select the “SORT” method.

Code:

Sub SortOption_VBA_Ex()
Range(“A1:C1”).Sort
End Sub

Now, let us look at the syntax of the Sort method in Range.

Range.Sort (Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, OrderCustom, MatchCase, Orientation, SortMethod, DataOption1, DataOption2, DataOption3)

To understand Sort option in VBA, we will discuss only these three main arguments:

• Key1: When we sort the data range using VBA, we must specify the column to be sorted. Taking the above example of the data range A1 to C1, to sort column B, Key1 will be considered as “B1” and will be written as Range(“B1”).
• Order1: represents the order of the selected Range “B1” as Ascending or Descending. It is available as two options, i.e., “xlAscending” or “xlDescending.”
• Header: This argument is used to specify if the data range has a header or not. It is available as “xlYes” if the header is present or “xlNo.”

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Examples

Let us look at examples for the Sort Range option in VBA and how to implement it.

Example #1

Let us take an example of sorting multiple columns one by one. Below is the data in an Excel worksheet of some customer details at a gym. First, let us look at the steps to sort this data.

• Step 1: Write the below data on an Excel sheet as shown below:

• Step 2: Open the Excel workbook and click on the “Developer tab” option. Select “Visual Basic” and open the VB Editor. In the “Insert” option, choose “Module” to open a new module.

• Step 3:
  • Start writing the sub-procedure.
  • Open the Range object.
  • Enter the cell address using double quotes.

• Step 4: Use the (.)dot and select the SORT method. Here, we wish to sort the column “A1” in ascending order. Hence,  our key1 is column “A1.”

• Step 5: Now, we must assign the order of sorting. Thus, assign “Order1” here as “xlAscending.”

• Step 6: Enter the Header argument as “xlYes”

• Step 7: Run the code either by using F5 or the Run option in the VB ribbon and see the result as shown below.

• Step 8: As we want to sort multiple columns using the Sort method, we now use column “B1” as our key2 and Order2 as Descending.

Sub VBASort_RangeEx1()
Range(“A1:C10”).Sort key1:=Range(“A1”), Order1:=xlAscending, key2:=Range(“B1”), Order2:=xlDescending
End Sub

• Step 9: Finally, enter the Header argument as “xlYes.”

Code:

Sub VBASort_Range_Ex1()
Range(“A1:C10”).Sort key1:=Range(“A1”), Order1:=xlAscending, key2:=Range(“B1”), Order2:=xlDescending, Header:=xlYes
End Sub

• Step 10: Run the code using F5 or the Run option in the VB ribbon and see the result below. Columns A1 and B1 are sorted in ascending and descending order, respectively.

Example #2

Let us look at an example where we sort multiple columns in one go. Here we will sort the Name and DOB columns simultaneously.

• Step 1: Write the below data in an Excel sheet.

• Step 2: Open the VB editor and start writing the sub-procedure. Use the Range object to define the range of columns to be sorted and define Key1 as A2, as we wish to sort product names alphabetically here.

• Step 3: Define the order as “xlAscending” and mention “xlNo” header, as we are not including the header in this example.

• Step 4:To sort the “Revenue generated” column, define another range with the Key1 as “C2.”

• Step 5: Assign Order1 again as “xlAscending” and Header as “xlNo” in the following format.

Code:

Sub VBASort_EX()
Range(“A2:A6”).Sort Key1:=Range(“A2”), _
Order1:=xlAscending, Header:=xlNo
Range(“C2:C6”).Sort Key1:=Range(“C2”), _
Order1:=xlAscending, Header:=xlNo
End Sub

• Step 6: Run the code by clicking “Run” or using F5 directly to see the results. We can see that the Product names and Revenue generated are sorted in Ascending order simultaneously.

Important Things to Note

• We can sort the data in Ascending or Descending order using the SORT method in VBA. The data can be sorted in Rows as well as in Columns.
• If we are unsure if the data has a Header, we can use “xlGuess” to analyze if the first line of the data is a Header or not.
• If we wish to include the header for sorting, use “xlYes”; if not, use “xlNo.”
• VBA sort ranges alphabetically using the “xlAscending” or “xlDescending” order in VBA.
• We can use multiple options in VBA code to customize the sort, namely, SortOn, Order, Data Option, MatchCase, Orientation, etc.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VBA Sort Range, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA Sort Range. Here we learn how to sort single & multiple columns using SORT option in Excel VBA, examples & downloadable template. You can learn more from the following articles –

• VBA Set Range
• VBA Selection Range
• VBA Subscript Out of Range