VBA to Sort Column in Excel (4 Methods)

To sort in Excel with VBA, you need to apply the Range.Sort method. In this article, we will show you how to sort the column in Excel with the Range.Sort method of VBA.

Range.Sort Method in Excel VBA

Range.Sort method in VBA sorts a range of values in Excel. Here Range is an object variable that specifies the range of cells that we want to sort in ascending or descending order.

Below are the parameters that you need to know about while working with this method.

VBA to Sort Column in Excel: 4 Methods

In this section, you will know how to sort a single column with and without a header, multiple columns with and without headers and how to sort just by double-clicking the header in a column in Excel.

1. Embed VBA to Sort a Single Column without Header in Excel

If you want to sort a single column in your Excel worksheet with VBA code then follow the steps below.

This is our column that we will sort with VBA code.

Steps:

• Press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

• In the pop-up code window, from the menu bar, click Insert -> Module.

• Copy the following code and paste it into the code window.

Sub SortSingleColumnWithoutHeader()
Range("B5:B15").Sort Key1:=Range("B5"), Order1:=xlAscending, Header:=xlNo
End Sub

Your code is now ready to run.

Here,

• Press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

You will see that your column is now sorted in ascending order.

Notice that here we have defined the data range manually as Range(“B5:B15”).

If you want to change data by adding or deleting values, you can implement the following code which automatically updates based on the cells in the dataset.

Sub SortSingleColumnWithoutHeader()
Range("B5", Range("B5").End(xlDown)).Sort Key1:=Range("B5"), Order1:=xlAscending, Header:=xlNo
End Sub

Notice that instead of defining range manually by Range(“B5:B15”), we have written, Range(“B5”, Range(“B5”).End(xlDown)).

This will sort the column based on the last consecutively filled cell in it. If there are blank cells, the data will only be considered up to the first blank cell.

Read More: Excel VBA to Sort Column Ascending

2. Insert VBA Macro to Sort a Single Column with Header

In the previous section, we had a dataset of a single column with no header, but now we have a column with a header.

This time we will learn how to sort it with VBA macro.

Steps:

• Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• In the code window, copy the following code and paste it.

Sub SortSingleColumnWithHeader()
Range("B5:B16").Sort Key1:=Range("B5"), Order1:=xlDescending, Header:=xlYes
End Sub

Your code is now ready to run.

Here,

• Run this code and you will get the column with header sorted in descending order.

Read More: Excel VBA to Sort by Column Header Name

3. VBA Macro to Sort Multiple Columns with or without Header

You can also sort multiple columns in your dataset with VBA code.

Steps:

• As previously shown, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• In the code window, copy the following code and paste it.

Sub SortMultipleColumnsWithHeaders()
With ActiveSheet.Sort
     .SortFields.Add Key:=Range("B4"), Order:=xlAscending
     .SortFields.Add Key:=Range("C4"), Order:=xlAscending
     .SetRange Range("B4:D15")
     .Header = xlYes
     .Apply
End With
End Sub

Your code is now ready to run.

Here,

.SortFields.Add Key:=Range(“B4”), Order:=xlAscending

.SortFields.Add Key:=Range(“C4”), Order:=xlAscending

By these two lines, we are defining Cell B4 and C4 to sort the two columns associated with them in ascending order.

As we have headers in our dataset so we specified Header = xlYes, otherwise we would have written Header = xlNo inside the code.

• Run this code and you will get the columns with header sorted in ascending order.

Read More: How to Sort Multiple Columns with Excel VBA

4. Macro to Sort Data by Double Clicking on Header in Excel

If you want to sort the data with ease just by double-clicking on the header, you can do that with VBA code.

Steps:

• Right-click on the sheet tab.
• From the appeared option list, click View Code.
• The code window will appear, copy the following code and paste it in there.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim iRange As Range
    Dim iCount As Integer
    iCount = Range("B4:D15").Columns.Count
    Cancel = False
    If Target.Row = 4 And Target.Column <= iCount Then
    Cancel = True
    Set iRange = Range(Target.Address)
    Range("B4:D15").Sort Key1:=iRange, Header:=xlYes
    End If
End Sub

• Save the code.

• Now go back to the worksheet of interest and if you double click on the headers you will see the columns are reorganizing.

Things to Remember

• You can create a named range and use it instead when you pass a range of cell references inside the Sort method. For example, if you want to sort range A1:A10, instead of passing it every time inside the code, you can create a named range of it, such as “SortRange” and use it with the Range.Sort method like Range(“SortRange”).
• If you are not sure whether your dataset has headers or not, you can let the system determine it by using the xlGuess parameter.

Download Workbook

You can download the free practice Excel workbook from here.

Conclusion

This article showed you how to sort the column in Excel VBA. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.

Related Articles

• How to Sort Range Using VBA in Excel
• Excel VBA Sort Named Range
• Excel VBA Sort Range with Multiple Keys
• Excel Macro: Sort Multiple Columns with Dynamic Range