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. Full Feature Free Trial 60-day!

Excel Productivity Tools

Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial

Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately. 60-day Unlimited Free Trial

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

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

Excel Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 80% Excel problems.

Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

Increases your productivity by 50% when viewing and editing multiple documents.
Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
Open and create documents in new tabs of same window, rather than in new windows.
Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!

You are guest ( Sign Up? )

or post as a guest, but your post won't be published automatically.

Loading comment... The comment will be refreshed after 00:00.

To post as a guest, your comment is unpublished.
Paula · 1 months ago
May I ask if there is a way to use this VBA code if there is a formula in the column that you want to be the main sort.

i.e. if A2 = the sum of f2 + g2+ h2 and column A needs to be sorted on the total sum? (I can't get VBA to work if formula in the cells)

Any assistance would be kindly appreciated as hope to use it for ranking races when the winner is the one with the total of three racing events.

Thank You
- Reply
- Share
- To post as a guest, your comment is unpublished.
  NABILA ISMAIL · 1 days ago
  
  i also want to know solution for Paula problem. please share
  
  Reply
  
  Share
To post as a guest, your comment is unpublished.
Bekir · 1 months ago
Hi,

How can I make this macro start after I fill all data to the specific column?
I want the listing macro start after I fill empty boxes. Because when I use this, it changes the place suddenly.
I need to start this macro after I filled the last data of that column (20 datas to be exact)
- Reply
- Share
To post as a guest, your comment is unpublished.
Rhea · 8 months ago
Hello, I am an intermediate Excel user. I am wondering if someone could help direct me for what I am trying to accomplish. Here is what I am trying to do: Auto-Sort with Custom Order.

A little about my spreadsheets: I have one sheet (named: Sheet1) that lists 16 columns (A through P) and currently 19 rows (this number will increase as more data is entered). In the last column (column P) all cells have a drop down menu of options (located and pulled from sheet two (named "BackEnd"). The drop down selections are color-coded (using conditional formatting rules).

What I am hoping to do: As a user enters data in a new row, as soon as they are finished selecting an option from the drop down menu in column P, Excel will auto-sort the new row to a specific order. I have been able to create VBA code for auto-sorting in ascending and descending order, but I have not been able to create a code for auto-sorting the data in a specific order. The order I would like to have the data sorted into is:

Unresponsive
Not Interested
Interested
Pre-Screened Not Qualified:
Pre-Screened Qualified
Application Pending
Application Approved
Initial Assessment:
Scope of Work:
Home Repairs in Progress
On Hold:
Complete
Other:

Is this possible? Any help is appreciated! Thank you!
- Reply
- Share
To post as a guest, your comment is unpublished.
Stephen · 1 years ago
While this is a fantastic code, I would like to know if anyone could help me get it go from ascending to descending? In other words, I'm looking for it to be the largest number on top with the lowest number on the bottom
- Reply
- Share
- To post as a guest, your comment is unpublished.
  Xander · 1 years ago
  
  Change (Order1:=xlAscending, Header:=xlYes, ) To (Order1:=xlDescending, Header:=xlYes, )
  
  Reply
  
  Share
To post as a guest, your comment is unpublished.
SHABEERALI · 1 years 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.
- Reply
- Share
- To post as a guest, your comment is unpublished.
  Tang Kelly · 1 years ago
  
  Hi,
  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,…
  
  Reply
  
  Share

Login to your account

Password Reset

Reminder