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:
Range(“A1”, Range(“A1”).End(xlDown)).Sort key1:=Range(“A1”), Order1:=xlAscending
Table of contents
- In combination with the Range object, VBA Sort Range is used to sort columns or rows in ascending or descending orders.
- VBA Sort Range descending order can be done using “xlDescending” in VBA code to sort data.
- Single or multiple columns are sorted within the Excel sheet using the VBA Sort range.
- The Excel worksheet needs to be activated before using Sort Range in VBA.
- Save the Excel worksheet in .xslm format to save the VBA code in modules.
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:
Follow the range with a dot. It will show the properties of Range. Select the “SORT” method.
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.”
Let us look at examples for the Sort Range option in VBA and how to implement it.
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.
Range(“A1:C10”).Sort key1:=Range(“A1”), Order1:=xlAscending, key2:=Range(“B1”), Order2:=xlDescending
- Step 9: Finally, enter the Header argument as “xlYes.”
Range(“A1:C10”).Sort key1:=Range(“A1”), Order1:=xlAscending, key2:=Range(“B1”), Order2:=xlDescending, Header:=xlYes
- 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.
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.
Range(“A2:A6”).Sort Key1:=Range(“A2”), _
Range(“C2:C6”).Sort Key1:=Range(“C2”), _
- 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)
There are four suitable ways to sort a dynamic range in Excel using VBA.
• Using “ascending” order in Excel macro to sort multiple columns in dynamic range.
• Using “Descending” order in Excel macro to sort multiple columns in dynamic range.
• Using multiple columns to sort along with “Header” in Excel Macro.
• Sorting multiple columns in Excel macro by using the “Worksheet” name.
Follow the below steps if the Excel VBA is not working:
• Activate the worksheet when we use the SORT method: To activate the worksheet, define the active worksheet in a sub-routine.
• Select a proper argument: Verify the input range.
The Boolean data type is used in VBA to sort True or False. True is represented by one and False by 0.
To sort data from smallest to largest in VBA, follow the below steps:
• Select the range to sort.
• Write a code in Excel defining the Order as Ascending and Run the code.
• The selected data will be sorted from smallest to largest.
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.
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 –