Log in


How to auto sort column by value in Excel?

For example, you have a purchase table as left screen shot shown. Now you want the Price column to be sorted automatically when you enter new number/price 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

Easily sort by frequency of occurrences in Excel

Kutools for Excel's Advanced Sort utility supports sorting data by text length, last name, absolute value, frequency, etc. in Excel quickly. Click for 60-day free trial!
ad sort by frequency 2

arrow blue right bubbleAuto 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, _
End If
End Sub

Note: 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.

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 entering new number in the Price column, you must enter the number in the first blank cell below original numbers. If there are blank cells between 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

Tip: In this Video, Kutools tab and Enterprise tab are added by Kutools for Excel. If you need it, please click here to have a 60-day free trial without limitation!

Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 200 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
People in conversation:
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    SHABEERALI · 21 days ago
    Any one know how to set set an auto serial no. for an excel work sheet. The number should excludes all heading and sub headings, and should auto re arrange after adding or deleting a row.
    • To post as a guest, your comment is unpublished.
      Tang Kelly · 19 days ago
      Kutools for Excel provides an Insert Sequence Number feature, which can insert unique and consecutive values no matter how many times you insert.
      For example, the first time you insert 1,2,3,4;
      The second time this feature will insert 5,6,7,8,9,10
      The third time this feature will insert 11,12,…
  • To post as a guest, your comment is unpublished.
    Sushant · 4 months ago
    Thanks for code and logic. The above logic is not working when we are looking at bigger range of cells like Intersect(Target, Range("B:B,C:C,D:D")). I want if there is change in any cell in the Column (B,C or D), resorting needs to be done.
  • To post as a guest, your comment is unpublished.
    Tarcisio · 5 months ago
    THANK YOU!!!
  • To post as a guest, your comment is unpublished.
    Logan · 5 months ago
    Copied the code exactly into my sheet in VBA. But nothing is happening am I missing something? Did exactly what you did.
  • To post as a guest, your comment is unpublished.
    Rezal · 8 months ago
    First row is excluded for the sorting