How to Sort a Column Using VBA in Excel (4 Methods)

Range.Sort Method in Excel VBA

The Range.Sort method in Excel VBA allows you to sort a range of values. The Range object variable specifies the cells you want to sort, either in ascending or descending order.

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

Method 1 – Sort a Single Column without Header 

• Open the Visual Basic Editor by pressing Alt + F11 or navigating to the Developer tab and clicking Visual Basic.

• In the code window, click Insert and choose Module.

• Copy and paste the following code:

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

Here,

• Press F5 or click the Run Sub/UserForm icon to execute the macro.

• Your column will be sorted in ascending order.

Note that the data range was defined manually as Range(“B5:B15”).

Note: If your data range changes dynamically (e.g., by adding or deleting values), use the following code instead:

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

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

This code sorts based on the last consecutively filled cell in the column, considering only non-blank cells.

Read More: Excel VBA to Sort Column Ascending

Method 2 – Sort a Single Column with Header

• Open the Visual Basic Editor as before.
• Insert a new module.
• Copy and paste this code:

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

Here,

• Run the code, and your column with a header will be sorted in descending order.

Read More: Excel VBA to Sort by Column Header Name

Method 3 – Sort Multiple Columns with or without Header

Follow these steps to sort multiple columns using VBA:

• Open the Visual Basic Editor by going to the Developer tab and clicking Visual Basic.
• Insert a new module in the code window.
• Copy and paste the following code:

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

Here,

.SortFields.Add Key:=Range(“B4”), Order:=xlAscending and .SortFields.Add Key:=Range(“C4”), Order:=xlAscending: Define cells B4 and C4 as the keys for sorting the associated columns in ascending order.

Since our dataset has headers, we specify Header = xlYes.

• Run this code, and your columns with header will be sorted in ascending order.

Read More: How to Sort Multiple Columns with Excel VBA

Method 4 – Sort Data by Double-Clicking on Header in Excel

If you want to sort data easily by double-clicking on the header, use this VBA code:

Steps:

• Right-click on the sheet tab.
• Select View Code from the options.
• In the code window, paste the following code

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.

• Return to the worksheet and double-click on the headers to see the columns reorganize.

Things to Remember

• You can create a named range (e.g., SortRange) and use it instead of specifying cell references directly in the Range.Sort method.
• If you’re unsure whether your dataset has headers, use the xlGuess parameter to let the system determine it.

Download Workbook

You can download the practice workbook from here:

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