[Excel VBA] How to Sort a Range in Ascending Order by a Specific Column (Easy Macro)

In this article, I will introduce a macro that uses Excel VBA to sort any range in ascending order based on a specific column. This helps eliminate manual work on your spreadsheets and ensures highly consistent data organization.

Prerequisites

• Excel Version: Microsoft 365 or 2016 and later
• Macro Location: Standard Module
• Target Data: Table format including a header row

Sample Code

Copy and paste the following code into a standard module to run it. The cell range and column numbers are set as examples, so you can use them as they are or adjust them to fit your needs.

Sub SortByTargetColumn()

    Dim tgtRange As Range      ' Range to be sorted

    ' Get the entire table including headers (Start cell D4)
    Set tgtRange = Range("D4").CurrentRegion

    ' Sort by column B (the 2nd column in the table) in ascending order
    With tgtRange
        .Sort key1:=.Columns(2), _
              Order1:=xlAscending, _
              Header:=xlYes
    End With

End Sub

Key Points of the Code

• Range("D4").CurrentRegion: This automatically selects the entire table, including the header row. You can change the starting cell (D4) to match your specific data location.
• Sorting Method: The .Sort method is used to sort the 2nd column of the table (.Columns(2)) in ascending order (xlAscending).
• Header:=xlYes: This setting ensures the header row is kept at the top and not included in the sort.

Advanced Examples

Frequently Asked Questions

Q: The sort stops because there are blank rows at the end of the table.

A: CurrentRegion stops selecting when it hits a blank row. Please delete the blank rows or manually specify the range (e.g., Range(“A1:C10”)).

Q: How do I handle data without a header row?

A: Change the setting to Header:=xlNo. This tells VBA to treat the entire range as data rows.

Summary

By using the VBA Sort method, you can execute sorting based on a specific column with just one click. First, try running this sample code to confirm it sorts correctly in ascending order. I hope you can adjust the start cell and column numbers to fit your business data and improve your work efficiency!