[Excel VBA] How to Sort by 4 or More Columns with Priority (Multi-Key Sort Macro)

Overview

This article explains how to create an Excel VBA macro that sorts data using 4 or more keys by utilizing the SortFields collection. While sorting by complex conditions manually takes time, using a macro allows you to reproduce the result with a single click.

Prerequisites

• Excel Version: Microsoft 365 or 2016 and later.
• Data Structure: A table with a header row (a range that can be acquired with CurrentRegion).
• Macro Location: Standard Module.

Sample Code

Paste the following code into a standard module and run it. The column order and numbers are for demonstration purposes, so please adjust them to fit your data.

Sub MultiKeySort()

    Dim tgtRange As Range          ' Range to be sorted
    Dim ws       As Worksheet      ' Target worksheet
    
    Set ws = ActiveSheet           ' Set the active sheet (change if needed)
    Set tgtRange = ws.Range("B2").CurrentRegion   ' Get the range including headers

    With ws.Sort
        ' Clear existing sort criteria
        .SortFields.Clear
        
        '--- Add sort keys (Order: Col 4 -> Col 1 -> Col 2 -> Col 7) ---
        .SortFields.Add Key:=tgtRange.Columns(4), SortOn:=xlSortOnValues, Order:=xlAscending
        .SortFields.Add Key:=tgtRange.Columns(1), SortOn:=xlSortOnValues, Order:=xlAscending
        .SortFields.Add Key:=tgtRange.Columns(2), SortOn:=xlSortOnValues, Order:=xlAscending
        .SortFields.Add Key:=tgtRange.Columns(7), SortOn:=xlSortOnValues, Order:=xlAscending
        
        '--- Configure sort settings ---
        .SetRange tgtRange
        .Header = xlYes            ' Range has headers
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply                      ' Apply the sort
    End With

End Sub

Code Explanation

• ws.Range(“B2”).CurrentRegion: Automatically selects the entire table. You can change the starting cell (e.g., “B2”) to match your data layout.
• .SortFields.Clear: Resets any existing sort conditions to prevent previous settings from interfering.
• .SortFields.Add: Calls this method 4 times to register the columns in priority order.
  • Key: Specifies the column to sort by.
  • Order: Specifies ascending (xlAscending) or descending (xlDescending) order.
• .SetRange / .Apply: Sets the target range and executes the sort.

Advanced Tips

Mixing Descending Order

If you want to sort a specific column in descending order, change the Order argument for that column to xlDescending.

Managing Sort Conditions with an Array

You can manage column numbers in an array (e.g., sortCols = Array(4, 1, 2, 7)) and loop through them to call .Add. This makes it easier to change the order later.

Sorting by 5 or More Columns

Simply add more .SortFields.Add lines to include additional columns.

FAQ

Q: What if my table does not have a header row? A: Change .Header = xlYes to .Header = xlNo.

Q: CurrentRegion is not selecting the correct range. A: This happens if there are empty rows or columns around the active cell. Delete the empty rows or explicitly specify the range, like Set tgtRange = ws.Range("B2:H100").

Summary

By using the SortFields collection, you can easily implement complex sorting with 4 or more columns in Excel VBA. Try running the sample code first to see if the data is sorted in the intended order. You can apply this to various business scenarios by adjusting the column numbers and sort order.