Code to automatically sort a list, each time that a new entry isadded to the bottom of the list

  • Thread starter Thread starter Mike C
  • Start date Start date
M

Mike C

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.
 
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.
Click to expand...

Hi Mike

Place the code below in the code sheet of the sheet containing your
list.

Private Sub Worksheet_Change(ByVal Target As Range)
Set isect = Intersect(Target, Range("A:A"))
If Not isect Is Nothing Then
Range("A:A").Sort Key1:=Range("A1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End If
End Sub

Regards,
Per
 
Hi Mike

Place the code below in the code sheet of the sheet containing your
list.

Private Sub Worksheet_Change(ByVal Target As Range)
Set isect = Intersect(Target, Range("A:A"))
If Not isect Is Nothing Then
    Range("A:A").Sort Key1:=Range("A1"), Order1:=xlAscending,
Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End If
End Sub

Regards,
Per
Click to expand...

Thanks Jessen. For some reason, the section that reads:

Range("A:A").Sort Key1:=Range("A1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

is giving me an error (i.e., turning red, reading "expression error")
as soon as I paste it into the worksheet's module.

Is this possibly because i have excel 2003?
 
Hi

The problem is wordwrap in your reader.

Remove the carriage return after xlAscending,

so the section is only two lines.
Thanks Jessen.  For some reason, the section that reads:

Range("A:A").Sort Key1:=Range("A1"), Order1:=xlAscending,
Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

is giving me an error (i.e., turning red, reading "expression error")
as soon as I paste it into the worksheet's module.

Is this possibly because i have excel 2003?- Skjul tekst i anførselstegn-

- Vis tekst i anførselstegn -
Click to expand...



Regards,
Per
 
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, that should all be one line of code. The automatic line wrap in the
news reader screwed it up.
 
Back
Top