Hi all,
I have a workbook with two sheets, one is my Lists sheet the other is my
Input sheet with a dynamic drop down. I found this code online but need some
help with it. everything I try breaks it.
the lists sheet has the following code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Columns(1).Sort Key1:=Range("A1"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End Sub


The Input (data validation) sheet has the following code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim ws As Worksheet
Dim i As Integer

Set ws = Worksheets("Lists")
If Target.Column = 3 And Target.Row 1 Then
If Application.WorksheetFunction.CountIf(ws.Range("Na meList"),
Target.Value) Then
Exit Sub
Else
i = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.Range("A" & i).Value = Target.Value
ws.Range("NameList").Sort Key1:=ws.Range("A1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End If

End Sub

The List on the list shedt is "NameList" and has the following source in
name define:
=OFFSET(Lists!$A$1,0,0,COUNTA(Lists!$A:$A),1)

What I need to do is break the Ascending sort without breaking the
functionality.\
I would like the drop down to remain dynamic but when a new entry is added
it goes to the last empty cell not a sorted cell.

Can someone help?? thanks, larry