Auto-sort when new data added when table is not at top?

[bh24524]

Hello, I have a table that feeds a Data Validation Menu. The table data is basically serving as a master list and its actual data starts in cell U9 (U8 is the header row but I don't want that sorted). Every time I add a new name to the bottom of the list, I'd like it to automatically sort that name within the list. What event code could I use that fits this specification?

 

[Anthony47]

With Office 2021 (or Office 365) you may use the function SORT to create a sorted list (and use this sorted list as data validation origin)

With older versions you need to use the Worksheet_Change event to start the sorting procedure

 

[Joe4]

You can use a Worksheet_Change event to do that.

Go to the sheet that has the data table that you are adding to, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code in the VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lr As Long

'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
    
'   Exit if update not made to column U, under row 8
    If Target.Column <> 21 Or Target.Row < 9 Then Exit Sub
    
'   Find last row in column U with data
    lr = Range("U8").End(xlDown).Row
    
'   Sort data
    Range("U8:U" & lr).Sort key1:=Range("U8"), order1:=xlAscending, Header:=xlYes
    
End Sub

This should automatically sort those entries as you add new entries to the bottom of column U.

 

[bh24524]

Thank you guys. I ended up using the VBA solution as the sheet already has some event procedures under the Worksheet_Change umbrella and it works as needed. I tried a code that I thought would work but it wasn't working. I wasn't even close after seeing this solution, so I'm glad I asked. Anthony47, I do have Office 365 as of very recently. Even though I went with the VBA solution, I am curious to know multiple ways of doing these things. In your solution with the function, would you end up ultimately with two lists on the sheet? A master list that you add to and then a formula that creates a sorted list that feeds the validation?

 

[Anthony47]

Anthony47, I do have Office 365 as of very recently. Even though I went with the VBA solution, I am curious to know multiple ways of doing these things. In your solution with the function, would you end up ultimately with two lists on the sheet? A master list that you add to and then a formula that creates a sorted list that feeds the validation?

Yes, your understanding is correct. If the SORT formula is inserted (for example) in Z1 then you will insert, as the origin of the validation list, the value =$Z$1#