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.

On 13 Apr., 07:04, Mike C wrote:
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.

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

On Apr 13, 12:21*am, Per Jessen wrote:
On 13 Apr., 07:04, Mike C wrote:

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.

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

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 -



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 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.

Mike, that should all be one line of code. The automatic line wrap in the
news reader screwed it up.

"Mike C" wrote:

On Apr 13, 12:21 am, Per Jessen wrote:
On 13 Apr., 07:04, Mike C wrote:

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.

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

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?