Note: The other languages of the website are Google-translated. Back to English
Log in Register

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


Kutools for Excel includes more than 300 handy tools for Excel, free to try without limitation in 30 days. Download and Free Trial Now!

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 30-day!
ad sort by frequency 2

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (35)
No ratings yet. Be the first to rate!
Alicia
Alicia
This comment was minimized by the moderator on the site
Beautiful, beautiful, beautiful, thank you SO much. Would have never been able to figure this out on my own and I really appreciate you sharing this!!!
Reply
Dean Marshall
Dean Marshall
This comment was minimized by the moderator on the site
I am looking for the same automatic sorting when entering data for my excel chart. Finding it hard to understand the details of what you have entered here as example. I would really appreciate it if someone here who did the example above could help me sort my sheet out so it did the same. I have a F1 chart that shows winners, and points, and would like it so that when the points are entered next to the drivers name for a specific race for the total on the right hand side of the sheet to be in numerical order, WITHOUT MIXING THE RESULT against other drivers. It seems that I can not place on this message an attachment of my chart so if you are able to alter my chart for me so this will happen I would like to hear from you. I can send it via email to you. If you can help with this then please email me at lordmarshall2013@gmail.com and in the SUBJECT line please write F1 Excel help and will reply with the chart attached for you. Thanks in advance for any help you can provide.
Reply
SK
SK
This comment was minimized by the moderator on the site
This is GREAT. This was of great help.
Reply
Brandon
Brandon
This comment was minimized by the moderator on the site
Say I need to auto sort column "C" and column "D". How would I modify the code to sort both columns?
Reply
nikos
nikos
This comment was minimized by the moderator on the site
Hello. Excellent trick, thank you very much. In my file, I have important info which I need to see in cells A1 and A2. Therefore, I have stabilized all cells and my values start from A3 to A500. When I execute this vba, my info in cell A2 goes in cell 501. How can I make it start ranging form cell A3? Thank you in advance!
Reply
Rezal
Rezal
This comment was minimized by the moderator on the site
First row is excluded for the sorting
Reply
Red Code
Red Code     Rezal
This comment was minimized by the moderator on the site
I had the same problem and I found out why. Go to line 5, (Order1:=xlAscending, Header:=xlYes, _). The script is telling Excel the first cell is a header, and therefore it will ignore it when the sorting takes place. If you want to include the first row, change it to the following line:


Order1:=xlAscending, Header:=xlNo, _
Reply
Report
Logan
Logan
This comment was minimized by the moderator on the site
Copied the code exactly into my sheet in VBA. But nothing is happening am I missing something? Did exactly what you did.
Reply
Tarcisio
Tarcisio
This comment was minimized by the moderator on the site
THANK YOU!!!
Reply
Sushant
Sushant
This comment was minimized by the moderator on the site
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.
Reply
SHABEERALI
SHABEERALI
This comment was minimized by the moderator on the site
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
Tang Kelly
Tang Kelly     SHABEERALI
This comment was minimized by the moderator on the site
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
Report
Stephen
Stephen
This comment was minimized by the moderator on the site
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
Xander
Xander     Stephen
This comment was minimized by the moderator on the site
Change (Order1:=xlAscending, Header:=xlYes, ) To (Order1:=xlDescending, Header:=xlYes, )
Reply
Rhea
Rhea
This comment was minimized by the moderator on the site
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
Bekir
Bekir
This comment was minimized by the moderator on the site
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
Paula
Paula
This comment was minimized by the moderator on the site
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
NABILA ISMAIL
NABILA ISMAIL     Paula
This comment was minimized by the moderator on the site
i also want to know solution for Paula problem. please share
Reply
Stan
Stan     Paula
This comment was minimized by the moderator on the site
For me it worked to change this line
If Not Intersect(Target, Range("B:B")) Is Nothing Then
I think the key is to set the Target Range to the fields that change, and keep the sort on the data with the summed results.
Reply
Adam
Adam     Paula
This comment was minimized by the moderator on the site
This works for me when there is a formula in the cell:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Range("B1").Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub
Reply
kerry
kerry
This comment was minimized by the moderator on the site
This has been really helpful but I have 2 rows of headers (rows 1 and 2) and when I run the VBA it takes the second row to the bottom of the dates
how do I stop this?
Reply
kellytte
kellytte     kerry
This comment was minimized by the moderator on the site
Hi kerry,

There is a VBA that will automatically sort cells in the Column igonring the first two header cells.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xIntRC As Integer
Dim xURg As Range
On Error Resume Next
If Not Intersect(Target, Range("B:B")) Is Nothing Then
If Not Intersect(Target, Range("B1:B2")) Is Nothing Then Exit Sub
Set xURg = ActiveSheet.UsedRange
xIntRC = xURg.Rows.count
Range("B2:B" & xIntRC).Sort Key1:=Range("B3"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End Sub
Reply
Report
Tilt
Tilt     kellytte
This comment was minimized by the moderator on the site
Hello, I am not having any luck with the code directly above from "kellytte" for sorting cells in the Column ignoring the first two header cells. Can anyone help me? Thanks.
Reply
javad
javad     kellytte
This comment was minimized by the moderator on the site
Hithis code solve my problem. but i need to sort column B & with this Just change column A, not all . help plz
Reply
edwin
edwin
This comment was minimized by the moderator on the site
Hi, it arrange the entire row but I have some link on cells into specific folder which is left behind after sort.
Reply
Dan
Dan
This comment was minimized by the moderator on the site
So this seems to work when the data is manually entered but doesn't work when it is a table that repopulates from another file....is there any way to do that?
Reply
Zoudan
Zoudan
This comment was minimized by the moderator on the site
Amazing thanks!!!
Reply
Steve
Steve
This comment was minimized by the moderator on the site
Hi,
This is extremely useful.
Is there a way to expand the formula to cover multiple columns? For example, to sort data based on values on first, column B, and then column C?I would really appreciate any solutions!
Reply
Steve_B
Steve_B
This comment was minimized by the moderator on the site
Hi,
This is extremely useful.
Is there a way to expand the formula to cover multiple columns? For example, to sort data based on values on first, column B, and then column C?
I would really appreciate any solutions!
Reply
Reported
There are no comments posted here yet
Load More
Leave your comments
Guest
Posting as Guest
Login
×
Rate this post:
Reset
0   Characters
Suggested Locations