ลบรายการซ้ำจาก Drop-Down List ใน Excel ด้วย VBA – คู่มือสมบูรณ์

ในบทความนี้ ฉันจะแสดงวิธีที่คุณสามารถ ลบค่าซ้ำจาก drop down list ของแผ่นงานใน Excel ด้วยความช่วยเหลือของ VBA และเก็บเฉพาะค่าที่ไม่ซ้ำกัน (unique values) คุณจะได้เรียนรู้การแยกค่าที่ไม่ซ้ำกันที่ปรากฏอย่างน้อยหนึ่งครั้ง และที่ปรากฏทีละครั้งเท่านั้น

Unique Values in a Drop Down List with Excel VBA (Quick View)

Sub Drop_Down_List_Unique_Values_At_Least_Once()
    List_Location = "B3"
    Data = Range(List_Location).Validation.Formula1
    Data = Split(Data, ",")
    Range(List_Location).Validation.Delete
    Unique_Data = ""
    Count = 0
    For i = LBound(Data) To UBound(Data)
        Unique_Values = Split(Unique_Data, ",")
        For j = LBound(Unique_Values) To UBound(Unique_Values)
            If Data(i) = Unique_Values(j) Then
                Count = 1
                Exit For
            End If
        Next j
        If Count = 0 Then
            If Unique_Data = "" Then
                Unique_Data = Unique_Data + Data(i)
            Else
                Unique_Data = Unique_Data + "," + Data(i)
            End If
        End If
        Count = 0
    Next i
    Range(List_Location).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
    Formula1:=Unique_Data
End Sub

วิธีเก็บค่าที่ไม่ซ้ำกันใน Drop Down List ด้วย Excel VBA

ที่นี่เรามี drop-down list ในเซลล์ B3 ของแผ่นงาน Excel ที่มีชื่อประเทศบางประเทศ

แต่ตามที่คุณเห็น ในรายการ บางชื่อถูกซ้ำ เช่น Germany ซ้ำ 3 ครั้ง และ Italy ซ้ำ 2 ครั้ง

วัตถุประสงค์ของเราวันนี้คือลบค่าซ้ำจาก drop-down list และเก็บเฉพาะค่าที่ไม่ซ้ำกัน

1. พัฒนา Macro เพื่อเก็บค่าที่ไม่ซ้ำกันที่ปรากฏอย่างน้อยหนึ่งครั้ง

ก่อนอื่น เราจะพัฒนา Macro เพื่อเก็บค่าที่ไม่ซ้ำกันที่ปรากฏอย่างน้อยหนึ่งครั้งใน drop-down list

ตัวอย่างเช่น สำหรับรายการที่กล่าวมา ผลลัพธ์ของ Macro จะเป็น Germany, Italy, France, England

⧭ โค้ด VBA:

Sub Drop_Down_List_Unique_Values_At_Least_Once()
    List_Location = "B3"
    Data = Range(List_Location).Validation.Formula1
    Data = Split(Data, ",")
    Range(List_Location).Validation.Delete
    Unique_Data = ""
    Count = 0
    For i = LBound(Data) To UBound(Data)
        Unique_Values = Split(Unique_Data, ",")
        For j = LBound(Unique_Values) To UBound(Unique_Values)
            If Data(i) = Unique_Values(j) Then
                Count = 1
                Exit For
            End If
        Next j
        If Count = 0 Then
            If Unique_Data = "" Then
                Unique_Data = Unique_Data + Data(i)
            Else
                Unique_Data = Unique_Data + "," + Data(i)
            End If
        End If
        Count = 0
    Next i
    Range(List_Location).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
    Formula1:=Unique_Data
End Sub

⧭ ผลลัพธ์:

รันโค้ด มันจะลบค่าซ้ำจาก drop-down list ของเซลล์ B3 ของแผ่นงานที่ใช้งานอยู่ และเก็บเฉพาะค่าที่ปรากฏอย่างน้อยหนึ่งครั้ง

⧭ หมายเหตุ:

อย่าลืมเปิดใช้งานแผ่นงานที่มี drop-down list ก่อนรันโค้ด นอกจากนี้ ให้เปลี่ยนการอ้างอิงเซลล์ของตำแหน่งรายการตามความต้องการของคุณก่อนรันโค้ด

อ่านเพิ่มเติม: วิธีสร้าง Drop Down List ด้วยค่าที่ไม่ซ้ำกันใน Excel (4 วิธี)

2. สร้าง Macro เพื่อเก็บค่าที่ไม่ซ้ำกันที่ปรากฏทีละครั้งเท่านั้น

ครั้งนี้ เราจะพัฒนา Macro เพื่อเก็บค่าที่ไม่ซ้ำกันที่ปรากฏทีละครั้งเท่านั้นใน drop-down list

ตัวอย่างเช่น สำหรับรายการที่กล่าวมา ผลลัพธ์ของ Macro จะเป็น France, England

⧭ โค้ด VBA:

Sub Drop_Down_List_Unique_Values_Exactly_Once()
    List_Location = "B3"
    Data = Range(List_Location).Validation.Formula1
    Data = Split(Data, ",")
    Unique_Data = ""
    Range(List_Location).Validation.Delete
    Count = 0
    For i = LBound(Data) To UBound(Data)
        For j = LBound(Data) To UBound(Data)
            If j <> i And Data(i) = Data(j) Then
                Count = 1
                Exit For
            End If
        Next j
        If Count = 0 Then
            If Unique_Data = "" Then
                Unique_Data = Unique_Data + Data(i)
            Else
                Unique_Data = Unique_Data + "," + Data(i)
            End If
        End If
        Count = 0
    Next i
    Range(List_Location).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
    Formula1:=Unique_Data
End Sub

⧭ ผลลัพธ์:

รันโค้ด มันจะลบค่าที่มีซ้ำออกจาก drop-down list ของเซลล์ B3 ของแผ่นงานที่ใช้งานอยู่ และเก็บเฉพาะค่าที่ปรากฏทีละครั้งเท่านั้น

⧭ หมายเหตุ:

อีกครั้ง อย่าลืมเปิดใช้งานแผ่นงานที่มี drop-down list ก่อนรันโค้ด นอกจากนี้ ให้เปลี่ยนการอ้างอิงเซลล์ของตำแหน่งรายการตามความต้องการของคุณก่อนรันโค้ด

เนื้อหาเกี่ยวข้อง: วิธีเลือกหลายรายการจาก Drop Down List ใน Excel (3 วิธี)

บทความคล้ายกัน:

• การสร้าง Drop Down Filter เพื่อดึงข้อมูลตามการเลือกใน Excel
• วิธีสร้าง Excel Drop Down List พร้อมสี (2 วิธี)
• Excel Drop Down List Not Working (8 ปัญหาและวิธีแก้)
• Auto Update Drop Down List ใน Excel (3 วิธี)
• VBA to Select Value from Drop Down List in Excel (2 Methods)

3. พัฒนา UserForm เพื่อใส่ค่าที่ไม่ซ้ำกันใน Drop Down List

สุดท้าย เราจะพัฒนา UserForm เพื่อลบค่าซ้ำจาก drop-down list และเก็บเฉพาะค่าที่ไม่ซ้ำกันด้วย VBA

⧪ ขั้นตอนที่ 1: เปิด UserForm

ไปที่ตัวเลือก Insert > UserForm ใน VBA editor เพื่อเปิด UserForm ใหม่ UserForm ใหม่ชื่อ UserForm1 จะเปิดขึ้น

⧪ ขั้นตอนที่ 2: ลากเครื่องมือไปที่ UserForm

ข้างๆ UserForm คุณจะได้ Toolbox เคลื่อนเคอร์เซอร์ไปที่ Toolbox และลาก 3 Labels และ 2 ListBoxes (ภายใต้ Label1 และ Label3) และ 1 TextBox (ภายใต้ Label2) ในลักษณะที่แสดงในรูป

สุดท้าย ลาก CommandButton ไปที่มุมล่างขวา

⧪ ขั้นตอนที่ 3: เขียนโค้ดสำหรับ ListBox1

ดับเบิลคลิกที่ ListBox1 Private Subprocedure ชื่อ ListBox1_Click จะเปิด ใส่โค้ดต่อไปนั้นที่นั่น

Private Sub ListBox1_Click()
    For i = 0 To UserForm1.ListBox1.ListCount - 1
        If UserForm1.ListBox1.Selected(i) = True Then
            Worksheets(UserForm1.ListBox1.List(i)).Activate
            Exit For
        End If
    Next i
End Sub

⧪ ขั้นตอนที่ 4: เขียนโค้ดสำหรับ TextBox1

จากนั้นดับเบิลคลิกที่ TextBox1 Private Subprocedure อีกอันชื่อ TextBox1_Change จะเปิด ใส่โค้ดต่อไปนี้ที่นั่น

Private Sub TextBox1_Change()
    On Error GoTo TB1:
    ActiveSheet.Range(UserForm1.TextBox1.Text).Select
    Exit Sub
TB1:
    x = 21
End Sub

⧪ ขั้นตอนที่ 6: เขียนโค้ดสำหรับ CommandButton1

สุดท้าย ดับเบิลคลิกที่ CommandButton1 Private Subprocedure ชื่อ CommandButton1_Click จะเปิด ใส่โค้ดต่อไปนี้ที่นั่น

Private Sub CommandButton1_Click()
    List_Location = UserForm1.TextBox1.Text
    Data = Range(List_Location).Validation.Formula1
    Data = Split(Data, ",")
    Range(List_Location).Validation.Delete
    Unique_Data = ""
    Count = 0
    If UserForm1.ListBox2.Selected(0) = True Then
        For i = LBound(Data) To UBound(Data)
            Unique_Values = Split(Unique_Data, ",")
            For j = LBound(Unique_Values) To UBound(Unique_Values)
                If Data(i) = Unique_Values(j) Then
                    Count = 1
                    Exit For
                End If
            Next j
            If Count = 0 Then
                If Unique_Data = "" Then
                    Unique_Data = Unique_Data + Data(i)
                Else
                    Unique_Data = Unique_Data + "," + Data(i)
                End If
            End If
            Count = 0
        Next i
    ElseIf UserForm1.ListBox2.Selected(1) = True Then
        For i = LBound(Data) To UBound(Data)
            For j = LBound(Data) To UBound(Data)
                If j <> i And Data(i) = Data(j) Then
                    Count = 1
                    Exit For
                End If
            Next j
            If Count = 0 Then
                If Unique_Data = "" Then
                    Unique_Data = Unique_Data + Data(i)
                Else
                    Unique_Data = Unique_Data + "," + Data(i)
                End If
            End If
            Count = 0
        Next i
    Else
        MsgBox "Select Either At Least Once or Exactly Once.", vbExclamation
    End If
    Range(List_Location).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
    Formula1:=Unique_Data
End Sub

⧪ ขั้นตอนที่ 7: เขียนโค้ดสำหรับรัน UserForm

แทรก Module ใหม่จาก VBA toolbar และใส่โค้ดต่อไปนี้ที่นั่น

Sub Run_UserForm()
    UserForm1.Caption = "Keep Unique Values in Drop-Down List"
    UserForm1.Label1.Caption = "Worksheet: "
    UserForm1.Label2.Caption = "List Location: "
    UserForm1.Label3.Caption = "Keep Unique Values that Appear: "
    UserForm1.ListBox1.BorderStyle = fmBorderStyleSingle
    UserForm1.ListBox1.ListStyle = fmListStyleOption
    For i = 1 To Sheets.Count
        UserForm1.ListBox1.AddItem Sheets(i).Name
    Next i
    For i = 0 To UserForm1.ListBox1.ListCount - 1
        If UserForm1.ListBox1.List(i) = ActiveSheet.Name Then
            UserForm1.ListBox1.Selected(i) = True
            Exit For
        End If
    Next i
    UserForm1.ListBox2.BorderStyle = fmBorderStyleSingle
    UserForm1.ListBox2.ListStyle = fmListStyleOption
    UserForm1.ListBox2.AddItem "At Least Once"
    UserForm1.ListBox2.AddItem "Exactly Once"
    UserForm1.CommandButton1.Caption = "OK"
    Load UserForm1
    UserForm1.Show
End Sub

⧪ ขั้นตอนที่ 8: การรัน UserForm (ผลลัพธ์สุดท้าย)

UserForm ของคุณพร้อมใช้งานแล้ว รัน Macro ชื่อ Run_UserForm

UserForm จะโหลดในแผ่นงาน

เลือกแผ่นงานที่ drop-down list อยู่ ที่นี่คือ Sheet3

จากนั้น ใส่การอ้างอิงเซลล์ของตำแหน่งรายการในแผ่นงาน ที่นี่คือ B3

สุดท้าย เลือก At Least Once หรือ Exactly Once ที่นี่ผมเลือก At Least Once

ดังนั้น UserForm ของผมมีลักษณะดังนี้:

จากนั้น คลิก OK คุณจะได้ค่าถูกลบออกจาก drop-down list ของตำแหน่งที่ป้อนตามเกณฑ์ที่เลือก

อ่านเพิ่มเติม: วิธีสร้าง Drop-Down List ตามสูตรใน Excel (4 วิธี)

สิ่งที่ควรจำ

• ในบทความนี้ ฉันเน้นที่การลบค่าซ้ำจาก drop-down list เท่านั้น หากคุณต้องการเรียนรู้วิธีสร้าง drop-down list หรือวิธีเรียงลำดับค่าในรายการซ้ำ คุณสามารถอ่านบทความนี้ได้

สรุป

ดังนั้น นี่คือวิธีการลบค่าซ้ำจาก drop-down list และเก็บเฉพาะค่าที่ไม่ซ้ำกันด้วย Excel VBA คุณมีคำถามไหม? รู้สึกสบายใจถามเราได้เลย และอย่าลืมเยี่ยมชมเว็บไซต์ ExcelDemy สำหรับโพสต์และอัปเดตเพิ่มเติม

บทความเกี่ยวข้อง

• วิธีเชื่อมโยงค่าเซลล์กับ Drop Down List ใน Excel (5 วิธี)
• Conditional Drop Down List ใน Excel (สร้าง เรียงลำดับ และใช้)
• วิธีสร้าง Dynamic Dependent Drop Down List ใน Excel
• วิธีใช้ IF Statement สร้าง Drop-Down List ใน Excel
• VLOOKUP with Drop Down List in Excel