Formatting Data Validation list lineup

[ABabeNChrist]

I’m using this code to update my data validation list and I want to change the way it updates the list at the present moment it sorts by ascending.

Code: Select all

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

    If Not Intersect(Range("A1"), Target) Is Nothing Then
        Cancel = True

        Dim inter As Range
        Dim cell As Range
        Dim r As Range
        Dim sVal As String

        On Error Resume Next
        Set inter = Intersect(Target, Cells.SpecialCells(xlCellTypeAllValidation))
        If inter Is Nothing Then Exit Sub
        For Each cell In inter
            If cell.Validation.Type <> xlValidateList Then Exit Sub

            sVal = cell.Validation.Formula1
            If Left(sVal, 1) <> "=" Then Exit Sub

            Set r = ThisWorkbook.Names(Mid(sVal, 2)).RefersToRange
            If r Is Nothing Then Exit Sub
            If IsNumeric(Application.Match(cell.Text, r, 0)) Then Exit Sub

            Cancel = True
            With r
                .Parent.Cells(Me.Rows.Count, .Column).End(xlUp)(2).Value = cell.Text
                .Resize(.Count + 1).Sort _
                        Key1:=r(1), Order1:=xlAscending, _
                        MatchCase:=False, Orientation:=xlTopToBottom, Header:=xlNo
            End With
        Next cell
    End If
End Sub

I would like to keep my current list in the order as it is, but with each new entry it would then add to bottom of existing list.
After reviewing this code, I replaced
xlAscending with xlNone is this correct

[HansV]

if you don't want to sort, remove or comment out the part

Code: Select all

                   .Resize(.Count + 1).Sort _
                        Key1:=r(1), Order1:=xlAscending, _
                        MatchCase:=False, Orientation:=xlTopToBottom, Header:=xlNo

[ABabeNChrist]

Thank you Hans