This post will guide you how to sort data by column values automatically with VBA Macro in Excel. How do I sort values automatically when a new value or data is entered in Excel 2013/2016. How to automatic sort data when data is updated in Excel.

Automatically Sorting Values When Data Changes

---

Assuming that you have a list of data in range B1:B5, in which contain sale values. And you want to sort those values in your data. You can use Sort function to sort those values. But when you add additional data or change the current values in this range, the order in the range will not be changed automatically.

For example, you try to add a new data set into the range B1:B5, it won’t change the order automatically. If you want to sort this range with new data, you need to re-perform the sort function.

So you may be think if there is a good way to automatically sort those values in Excel. Of course, the answer is yes. You can use a VBA Macro to achieve the result. Here are the steps:

#1 right click on the sheet tab, and select view code from the popup menu list. And the Microsoft Visual Basic for Applications window will appear.

#2 copy the following codes into the code window in VBA window. Save and close it.

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

#3 you can go back to the original worksheet, and enter a new sale value in Cell B6 or you can also modify the original sale values. You should notice that the values will be sorted automatically.

 

The above VBA Macro code can be used to sort a single column with Header in Excel. And if you want to sort a single column without header, you can use the following codes:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Range("B1", Range("B1").End(xlDown)).Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlNo
End Sub