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 <js2k...@yahoo.com> 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 <perjesse...@hotmail.com> wrote:
> On 13 Apr., 07:04, Mike C <js2k...@yahoo.com> 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" <(E-Mail Removed)> wrote in message
news:269119e8-37ad-4694-81c6-(E-Mail Removed)...
> 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 <perjesse...@hotmail.com> wrote:
> > On 13 Apr., 07:04, Mike C <js2k...@yahoo.com> 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?
>
>