Code to automatically sort a list, each time that a new entry is added to the bottom of the list
Try this, as written:
- Sort should update after user leaves the cell (could update immediately on
change but this way allows quick re-edit/undo of the cell).
- If there is a gap or empty cell in the range and the changing cell is
below the gap nothing will happen (allows for a formula cell to be written 2
or more cells below the range)
- if user changes a cell that's already in the sorted range the range will
also re-sort (after leaving the cell)
following goes in the Worksheet module (rt-click sheet tab, view code)
Dim mLastLastRow As Long
Dim msLastActiveAddr As String
Dim mRng2Sort As Range
Const sTopCell As String = "A2" ' << CHANGE
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
UpdateSorter
End Sub
Public Function UpdateSorter()
Dim sLast As String
Dim nRow As Long
Dim cel As Range
Set cel = ActiveCell
sLast = cel.Address
If sLast < msLastActiveAddr Then
msLastActiveAddr = sLast
If Not mRng2Sort Is Nothing Then
mRng2Sort.Sort Key1:=mRng2Sort(1, 1), _
Order1:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End If
With Range(sTopCell)
If cel.Column = .Column Then
nRow = .End(xlDown).Row
If nRow = Rows.Count Then nRow = .Row
If cel.Row = .Row And cel.Row <= nRow + 1 And nRow .Row Then
Set mRng2Sort = Range(Range(sTopCell), Cells(nRow, .Column))
Else
Set mRng2Sort = Nothing
End If
End If
End With
End Function
Regards,
Peter T
"Mike C" wrote in message
...
Hello - I am wondering if anyone could provide me with code that will
re-sort a list, each time a user places a new line at the bottom of
the list.
So, for example, in column A, rows 1-10, I have a list of names, which
are sorted descending. When a new name is added to row 11, and the
user clicks out off of the cell or presses enter, I am hoping the
column can automatically resort.
Thanks for any suggestions.
|