I'm updateing data validation list and I useing this VBA code and that
updates my list. The problem I'm haveing is I want the data to go to the end
of the list. Can anyone help me with this VBA code. Table is my list name.
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("table")
If Target.Column = 4 And Target.Row 1 Then
If Application.WorksheetFunction.CountIf(ws.Range("ta ble"), 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("table").Sort Key1:=ws.Range("A1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End If

End Sub
Thanks for your help,

Hi Larry

If you want the value to remain at the end of your list, remove the Sort
routine.

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

Set ws = Worksheets("table")
If Target.Column = 4 And Target.Row 1 Then
If Application.WorksheetFunction.CountIf(ws.Range("ta ble"), _
Target.Value) Then
Exit Sub
Else
i = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.Range("A" & i).Value = Target.Value
End If
End If

End Sub

--
Regards
Roger Govier

"Larry" wrote in message
...
I'm updateing data validation list and I useing this VBA code and that
updates my list. The problem I'm haveing is I want the data to go to the
end
of the list. Can anyone help me with this VBA code. Table is my list name.
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("table")
If Target.Column = 4 And Target.Row 1 Then
If Application.WorksheetFunction.CountIf(ws.Range("ta ble"), 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("table").Sort Key1:=ws.Range("A1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End If

End Sub
Thanks for your help,

Thanks Roger
Can you tell which one is the sort routine

"Roger Govier" wrote:

Hi Larry

If you want the value to remain at the end of your list, remove the Sort
routine.

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

Set ws = Worksheets("table")
If Target.Column = 4 And Target.Row 1 Then
If Application.WorksheetFunction.CountIf(ws.Range("ta ble"), _
Target.Value) Then
Exit Sub
Else
i = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.Range("A" & i).Value = Target.Value
End If
End If

End Sub

--
Regards
Roger Govier

"Larry" wrote in message
...
I'm updateing data validation list and I useing this VBA code and that
updates my list. The problem I'm haveing is I want the data to go to the
end
of the list. Can anyone help me with this VBA code. Table is my list name.
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("table")
If Target.Column = 4 And Target.Row 1 Then
If Application.WorksheetFunction.CountIf(ws.Range("ta ble"), 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("table").Sort Key1:=ws.Range("A1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End If

End Sub
Thanks for your help,

Roger Thanks for the help it now works.
One more question can I deled the data useing this same VBA code.
Thanks Again for your help

"Roger Govier" wrote:

Hi Larry

If you want the value to remain at the end of your list, remove the Sort
routine.

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

Set ws = Worksheets("table")
If Target.Column = 4 And Target.Row 1 Then
If Application.WorksheetFunction.CountIf(ws.Range("ta ble"), _
Target.Value) Then
Exit Sub
Else
i = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.Range("A" & i).Value = Target.Value
End If
End If

End Sub

--
Regards
Roger Govier

"Larry" wrote in message
...
I'm updateing data validation list and I useing this VBA code and that
updates my list. The problem I'm haveing is I want the data to go to the
end
of the list. Can anyone help me with this VBA code. Table is my list name.
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("table")
If Target.Column = 4 And Target.Row 1 Then
If Application.WorksheetFunction.CountIf(ws.Range("ta ble"), 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("table").Sort Key1:=ws.Range("A1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End If

End Sub
Thanks for your help,