Excel VBA Sort Range – Columns Ascending – or Alphabetically

Excel VBA sorting Range of multiple Columns

How to Sort a Excel range of data? Do it either thru

1. VBA Sort Range code or
2. Built-in menu option.

I have presented both the methods, using built in option and also using the vba code.

1. VBA Code To Sort Excel Range

This would help in sorting multiple columns of data in your Excel range.

Start with clearing the existing sort keys, then defining new keys to sort, fields to be sorted, etc.,

Also Read: How to do a simple Sort just by clicking a Column Header?

'--------------------------------------------------------------------------------
'Visit https://officetricks.com to get more Free & Fully Functional VBA Codes
'--------------------------------------------------------------------------------
Public Sub Sort_Col(tsh As Worksheet, keyRange As Range, sortRange As Range, xlHeaderYesNo As XlYesNoGuess)
    'Clear Existing Sort Keys
    tsh.Sort.SortFields.Clear
    
    'Add new sorting key
    tsh.Sort.SortFields.Add Key:=keyRange _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
        
    'Add if you have more than one sorting key - Multiple Column Sort
    'tsh.Sort.SortFields.Add Key:=keyRange _
    '    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    '    xlSortNormal
        
    'Sort the Excel Range
    With tsh.Sort
        .SetRange sortRange
        .Header = xlHeaderYesNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    'Process Completed
End Sub

This is almost the same code as how you would get when you record macro before using the builtin sort option that is explained below.

Quick Tip: You can try to record macro option before performing the builtin Excel Sort option. This would generate the quick vba macro code for you.

This code can also be modified as per your need.

2. Built in Excel Sort Columns Data Option

In Excel to Sort range of data, first select all the columns or cells in this range. Then follow these steps:

1. Choose “Data” in Excel Menu
2. Select “Sort” in Sort & filter section
3. Choose the column to sort & sorting order, whether data has header, Sort based on color or property.
4. Click OK.

Assume you have this data in a worksheet in Column A.

Excel will sort the cells based on the selection you have chosen from the above steps and will display the results in place. Here it is only one column. but the same steps can also be used to Sort multiple columns.Follow the steps explained above.

In case, you would like to automate this sorting by just with a mouse click on the columns headers, then refer this article. It explains how to sort a column just by clicking on the Excel column header.

In that article also, the code is pretty much similar, but the implementation part differs. It uses events to capture the mouse click and trigger the activity accordingly.