How to Sort Range Using VBA in Excel (6 Examples) - ExcelDemy (2023)

Knowing how to sort range using VBA in Excel is time and effort-saving in our daily calculations. Although Excel provides a sorting facility by default. By using the Range.Sort method, we get access to several parameters to sort a dataset with more options than usual.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Introduction to the Range.Sort Statement in Excel VBA

Objective: To sort a range of cell data.

Syntax:
expression.Sort (Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, OrderCustom, MatchCase, Orientation, SortMethod, DataOption1, DataOption2, DataOption3)
Here, the expression represents a Range object i.e., a cell, a row, a column, or a selection of cells.

Arguments:
We need to provide three main parameters for the Range.Sort method. They are-
Key– The range of cells from single or multiple columns we need to sort.
Order– Specify the sorting order either ascending or descending.
Header– Declare whether the columns to be sorted have a header or not.

6 Examples to Sort Range in Excel VBA

In this article, as a dataset, we’ll use a list of peoples’ names with their date of birth and age. We’ll apply different methods to sort the dataset. Let’s go through the article and practice to master these methods.

1. Sort Single Column Range Using Excel VBA

In this example, we’ll sort people from oldest to youngest. Let’s follow the steps to use the Range.Sort method that will sort the Age column in descending order.

Steps:

• Go to the Developer Tab in the Excel Ribbon to click on the Visual Basic

• Then choose the Module option from the Insert tab to open a new module.

Now, we’ll put our code to sort the Age column range.

1.1 Column with Header

Put the following code in the visual code editor.

Sub SortRange()Range("D4:D11").Sort Key1:=Range("D4"), _ Order1:=xlDescending, _ Header:=xlYesEnd Sub

Press F5 or click the Run button to execute the code.

Explanation:
In the above code, we put-
Expression (Range object)=Range(“D4:D11”); the age column with a header in cell D4 and values in D5:D11.
Key = Range(“D4”); the key for sorting.
Order= xlDescending; as we want to sort values from largest to lowest we set the sorting order as descending.
Header =xlYes; In the following screenshot, we can see that the dataset has a header for each of the columns.

1.2 Column without Header

Put the following code in the visual code editor.

Sub SortRange()Range("D4:D10").Sort Key1:=Range("D4"), _ Order1:=xlDescending, _ Header:=xlNoEnd Sub

Press F5 or click the Run button to execute the code.

Explanation:
In the above code, we put-
Expression (Range object)=Range(“D4:D10”); the age column without a header has values in D4:D10.
Key = Range(“D4”); the key for sorting.
Order= xlDescending; as we want to sort values from largest to lowest we set the sorting order as descending.
Header =xlNo; In the following screenshot, we can see that the dataset has no header.

Related Content: How to Sort Columns in Excel without Mixing Data (3 Ways)

2. Using VBA Code to Sort Multiple Column Range in Excel

To show sorting in multiple columns, we need to modify our dataset a little. We inserted a few new rows. In the modified dataset, rows 7, 8, and 9 have the same values for the date of birth and ages but three different names. These names are not in any specific order of ascending or descending.

In this example, we’ll order the names in ascending order. Let’s run the following code in the visual basic editor:

Sub SortRange()Range("B4:D12").Sort Key1:=Range("D4"), _ Order1:=xlDescending, _ Key2:=Range("B4"), _ Order2:=xlAscending, _ Header:=xlYesEnd Sub

Explanation:
In the above screenshot, we can see that the ages in column D are sorted in descending order. We added two more parameters in our previous code.
Key2: =Range(“B4”), the key to sort names.
Order2: =xlAscending, the order for shorting names.
As a result, we see the names in rows 7, 8, and 9 are now alphabetically sorted in ascending order.

In the following screenshot, we changed the value of the Order2 parameter to sort the names in descending order.

Read More: How to Sort Multiple Columns in Excel (5 Quick Approaches)

3. Double Click on the Header to Sort Column Range in Excel VBA

Excel’s default sorting feature doesn’t allow to sort values of a column by double-clicking the column header. But using VBA code we can make it happen. Let’s illustrate this functionality by applying the following code.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)Dim KeyRange As RangeDim ColCount As IntegerColCount = Range("A1:C8").Columns.CountCancel = FalseIf Target.Row = 1 And Target.Column <= ColCount ThenCancel = TrueSet KeyRange = Range(Target.Address)Range("A1:C8").Sort Key1:=KeyRange, Header:=xlYesEnd IfEnd Sub

In this code, we used the BeforeDoubleClick event to disable the usual double–click which is to start the editing mode of the cell. With this event running, if we double–click on any of the column headers it sorts the column data in ascending order.

Read More: VBA to Sort Column in Excel (4 Methods)

Similar Readings:

• How to Add Sort Button in Excel (7 Methods)
• Sort Unique List in Excel (10 Useful Methods)
• How to Use Sort Function in Excel VBA (8 Suitable Examples)
• Sort Duplicates in Excel (Columns and Rows)
• Random Sort in Excel (Formulas + VBA)

4. Sort Column Range Based on Background Color Using Excel VBA

We can sort a range of cells in a column based on their background color. To do so, we need to add a parameter named SortOn which has a value xlSortOnCellColor. To demonstrate the sorting, we first set different background colors to the rows of our dataset.

Then in the visual basic code editor copy the following code and press F5 to run it.

Sub SortRangeByBackgroundColor()ActiveWorkbook.Worksheets("background").Sort.SortFields.Add2 Key:=Range("B4"), _ SortOn:=xlSortOnCellColor, Order:=xlAscending, DataOption:=xlSortNormalWith ActiveWorkbook.Worksheets("background").Sort .SetRange Range("B4:D10") .ApplyEnd WithEnd Sub

In the following screenshot, we can see the sorted dataset based on their background color.

Explanation:

• In this example, we named the worksheet “background”. So, in the code, we put “background” as our active worksheet name.
• We set B4 as the key and B4:D10 as the range. The code will sort data based on the key.
• As we didn’t specify the header parameter, the code runs for the default no header.
• We set the order parameter as ascending, so it sorted the data from lower to higher values.

Read More: How to Sort by Color in Excel (4 Criteria)

5. Apply VBA Code to Sort Column Range Based on Font Color

By applying VBA code, we can sort our dataset based on their font color. First, we need to color different rows to illustrate the example.

Apply the code below to sort the dataset based on font color.

Sub SortRangeByFontColor()ActiveWorkbook.Worksheets("fontcolor").Sort.SortFields.Add(Range("B4"), _ xlSortOnFontColor, xlAscending, xlSortNormal).SortOnValue.Color = RGB(0, 0, 0)With ActiveWorkbook.Worksheets("fontcolor").Sort .SetRange Range("B4:D11") .Header = xlYes .Orientation = xlTopToBottom .ApplyEnd WithEnd Sub

Explanation:

• In this example, we named the worksheet “fontcolor”. So, in the code, we put “fontcolor” as our active worksheet name.
• We set B4 as the key and B4:D11 as the range. The code will sort data based on the key.
• In this example, we also specified the header parameter as xlYes.
• Here, we set the order parameter as ascending, so it sorted the data from lower to higher values.
• The value of the SortOn parameter is
• The orientation parameter holds the value xlTopToBottom as it is mandatory.
• Color to sort on is in RGB terms which has a value from 0 to 255.

Read More: How to Sort Two Columns in Excel to Match (Both Exact and Partial Match)

6. Change Orientation to Sort Range Using Excel VBA

Using the orientation parameter, we can change the way we want to sort data. In this example, we have transposed our dataset to sort it horizontally.

Let’s put the following code in the visual basic editor and press F5 to run it.

Sub Orientation()Range("B4:H6").Sort Key1:=Range("B6"), _ Order1:=xlAscending, _ Orientation:=xlSortRows, _ Header:=xlYesEnd Sub

Here we sorted the data based on the age row in ascending order from left to right. In the code, we set the orientation parameter as xlSortRows.

Related Content: How to Auto Sort Multiple Columns in Excel (3 Ways)

Things to Remember

• The SortOn parameter that we used to sort column range based on background color and font color can only be used by a worksheet object. We cannot use it with a range object.
• The BeforeDoubleClick event sorts data only in ascending.

Conclusion

Now, we know how to sort range using VBA in Excel. Hopefully, it would encourage you to use this more confidently. Any questions or suggestions don’t forget to put them in the comment box below.

Related Articles

• How To Sort Alphabetically In Excel And Keep Rows Together
• Auto Sort When Data is Entered in Excel (3 Methods)
• How to Sort Alphabetically in Excel with Multiple Columns (4 Methods)
• Sort by Last Name in Excel (4 Methods)
• [Solved!] Excel Sort Not Working (2 Solutions)