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.
Recommended Productivity Tools
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.
Recommended Productivity Tools
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
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...
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 2 months agoWhile 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
To post as a guest, your comment is unpublished.· 6 months agoAny 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.· 6 months agoHi,
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.· 10 months agoThanks 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.· 11 months agoTHANK YOU!!!
To post as a guest, your comment is unpublished.· 11 months agoCopied the code exactly into my sheet in VBA. But nothing is happening am I missing something? Did exactly what you did.