Decreasing validation list

[YasserKhalil]

Hello
I want to make the validation list decreasing as soon as selecting item from the list
Say I have MyList named range with some values such as Yasser1 , Yasser2 and so on
I want simply when selecting Yasser1 not to appear again in the list
Here's an attachment

[alansidman]

Look at this link. While it is about table seating at a party, it employs the feature you are looking for.

http://blog.contextures.com/archives...ng-with-excel/

[YasserKhalil]

Thanks for the quick reply
I want to solve this problem by vba not formulas

[YasserKhalil]

Is there a way for doing that task by code?

[YasserKhalil]

No reply till now. Is this kind of thread new or difficult?
I found a code but I don't like it as it removes the original list ...!!

[alansidman]

I googled "vba listbox delete selected item" and found several items of interest.

Here is the first one to look at:

http://www.ozgrid.com/forum/showthread.php?t=27094

[YasserKhalil]

I mean Validation list not listbox ..
This is the code I found but it removes the original list

Private Sub Worksheet_Change(ByVal Target As Range)

Dim strVal As String

Dim strEntry As String



    On Error Resume Next

        strVal = Target.Validation.Formula1

            If Not strVal = vbNullString Then

                strEntry = Target

                Application.EnableEvents = False

                With Sheet1.Range("MyList")

                    .Replace What:=strEntry, Replacement:="", _
                      LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False

                    .Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlNo, _
                      OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

                    .Range("A1", .Range("A65536").End(xlUp)).Name = "MyList"

                End With

            End If

        Application.EnableEvents = True

    On Error GoTo 0

End Sub

As an idea but I can't figure it out .. store my original list and deal with the stored array not with th origninal list!!

[YasserKhalil]

Thanks everybody
I found the solution at this link
http://www.ozgrid.com/forum/showthread.php?t=192696
thanks a lot