How to auto sort column by value in Excel?

For example, you have a purchase table as below screenshot shown. Now you want the Price column to be sorted automatically when you enter new numbers/prices in this column, how could you solve it? Here I introduce a VBA macro to help you auto sort a specific column by value in Excel.

Auto sort column by value with VBA

Auto sort column by value with VBA

This VBA macro will sort all data in a specific column automatically as soon as you enter new data or change value in the column in Excel.

1. Right click current sheet name in the "Sheet Tab bar", and then click the "View Code" from the right-clicking menu.

2. In the opening "Microsoft Visual Basic for Application" dialog box, paste the following VBA macro code into the opening window.

VBA: Auto Sort Column in Excel

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Range("B1").Sort Key1:=Range("B2"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End Sub

Note:
1) In the above vba code, "B:B" means it will auto-sort Column B, B1 is the first cell in Column B, "B2" is the second cell in Column B, and you can change them based on your needs.
2) The snippet "Header:=xlYes" in the 5th row tells Excel that the range you will sort has a header, so that the first row of the range will not be included when sorting. If there is no header, please change it to "Header:=xlNo"; and change "Key1:=Range("B2")" in the 4th row to "Key1:=Range("B1")".

3. Then go back to the worksheet, when you enter a new number in the Price column or modify any existing prices, the Price column will be automatically sorted in ascending order.

Note: When you enter a new number in the Price column, you must enter the number in the first blank cell below the original numbers. If there are blank cells between the new entered number and original numbers as well as blank cells between original numbers, this column won't be sorted automatically.

Demo: Auto sort column by value with VBA in Excel