ค่าที่ไม่ซ้ำในรายการแบบหล่นลงที่มี VBA ใน Excel (คู่มือฉบับสมบูรณ์)

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

ค่าที่ไม่ซ้ำในรายการแบบหล่นลงด้วย Excel 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

วิธีเก็บค่าที่ไม่ซ้ำในรายการแบบหล่นลงด้วย Excel VBA

เรามีรายการดรอปดาวน์ในเซลล์ B3 ของแผ่นงาน Excel ที่มีชื่อของบางประเทศ

แต่อย่างที่คุณเห็นในรายการ มีบางชื่อซ้ำกัน เช่นเดียวกับเยอรมนีสามครั้งและอิตาลีซ้ำสองครั้ง

เป้าหมายของเราในวันนี้คือการลบค่าที่ซ้ำกันออกจากรายการแบบเลื่อนลงและเก็บเฉพาะค่าที่ไม่ซ้ำเท่านั้น

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

ก่อนอื่น เราจะพัฒนา มาโคร เพื่อคงค่าที่ไม่ซ้ำกันซึ่งปรากฏอย่างน้อยหนึ่งครั้งในรายการแบบเลื่อนลง

ตัวอย่างเช่น สำหรับรายการที่กล่าวมาข้างต้น ผลลัพธ์ของ มาโคร จะเป็น เยอรมนี อิตาลี ฝรั่งเศส อังกฤษ .

VBA รหัสสำหรับวัตถุประสงค์นี้จะเป็น:

⧭ รหัส 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

⧭ ผลลัพธ์:

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

⧭ หมายเหตุ:

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

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

2. การสร้างมาโครเพื่อรักษาค่าที่ไม่ซ้ำในรายการแบบหล่นลงที่ปรากฏเพียงครั้งเดียว

ครั้งนี้เราจะพัฒนา มาโคร เพื่อคงค่าที่ไม่ซ้ำกันซึ่งปรากฏเพียงครั้งเดียวในรายการแบบเลื่อนลง

ตัวอย่างเช่น สำหรับรายการที่กล่าวมาข้างต้น ผลลัพธ์ของ มาโคร จะเป็นฝรั่งเศส อังกฤษ .

VBA รหัสสำหรับวัตถุประสงค์นี้จะเป็น:

⧭ รหัส 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

⧭ ผลลัพธ์:

เรียกใช้รหัส จะลบค่าที่ซ้ำกันออกจากรายการแบบเลื่อนลงของเซลล์ B3 ของแผ่นงานที่ใช้งานอยู่และเก็บเฉพาะค่าที่ปรากฏเพียงครั้งเดียวเท่านั้น

⧭ หมายเหตุ:

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

เนื้อหาที่เกี่ยวข้อง: วิธีการสร้างการเลือกหลายรายการจากรายการดรอปดาวน์ใน Excel (3 วิธี)

การอ่านที่คล้ายกัน:

• การสร้างตัวกรองแบบเลื่อนลงเพื่อดึงข้อมูลตามการเลือกใน Excel
• วิธีสร้างรายการดรอปดาวน์ของ Excel พร้อมสี (2 วิธี)
• รายการแบบหล่นลงของ Excel ไม่ทำงาน (8 ปัญหาและวิธีแก้ไข)
• อัปเดตรายการแบบเลื่อนลงอัตโนมัติใน Excel (3 วิธี)
• VBA เพื่อเลือกค่าจากรายการดรอปดาวน์ใน Excel (2 วิธี)

3. การพัฒนา UserForm เพื่อใส่ค่าที่ไม่ซ้ำในรายการแบบหล่นลง

สุดท้าย เราจะพัฒนา ฟอร์มผู้ใช้ เพื่อลบค่าที่ซ้ำกันออกจากรายการแบบเลื่อนลงและเก็บเฉพาะค่าที่ไม่ซ้ำกับ VBA .

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

ไปที่ แทรก> UserForm ตัวเลือกใน VBA ตัวแก้ไขเพื่อเปิด ฟอร์มผู้ใช้ . ใหม่ . ฟอร์มผู้ใช้ใหม่ เรียกว่า UserForm1 จะเปิดขึ้น

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

นอกจาก ฟอร์มผู้ใช้ คุณจะได้รับ กล่องเครื่องมือ . เลื่อนเคอร์เซอร์ของคุณไปที่ กล่องเครื่องมือ แล้วลาก 3 ป้าย และ 2 กล่องรายการ (ภายใต้ Label1 และ Label3 ) และ 1 กล่องข้อความ (ภายใต้ Label2 ) ในลักษณะที่แสดงในรูป

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

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

ดับเบิลคลิกที่ ListBox1 . ขั้นตอนย่อยส่วนตัว เรียกว่า 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 . ขั้นตอนย่อยส่วนตัว Another เรียกว่า 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 . ขั้นตอนย่อยส่วนตัว เรียกว่า 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

แทรก โมดูลใหม่ จาก แถบเครื่องมือ VBA และใส่รหัสต่อไปนี้ที่นั่น

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 (ผลลัพธ์สุดท้าย)

ฟอร์มผู้ใช้ .ของคุณ พร้อมใช้งานแล้ว เรียกใช้ มาโคร เรียกว่า Run_UserForm .

ฟอร์มผู้ใช้ จะถูกโหลดลงในใบงาน

เลือกเวิร์กชีตที่มีรายการดรอปดาวน์อยู่ นี่คือ Sheet3 .

จากนั้นป้อนการอ้างอิงเซลล์ของตำแหน่งของรายการในเวิร์กชีต นี่คือ B3 .

สุดท้าย เลือก อย่างน้อยหนึ่งครั้ง หรือ เพียงครั้งเดียว ที่นี่ฉันได้เลือกอย่างน้อยหนึ่งครั้ง .

ดังนั้น ฟอร์มผู้ใช้ . ของฉัน มีลักษณะดังนี้:

จากนั้นคลิก ตกลง . คุณจะได้รับค่าที่ซ้ำกันถูกลบออกจากรายการดรอปดาวน์ของตำแหน่งอินพุตตามเกณฑ์ที่คุณเลือก

อ่านเพิ่มเติม: วิธีการสร้างรายการแบบหล่นลงตามสูตรใน Excel (4 วิธี)

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

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

บทสรุป

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

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

• วิธีเชื่อมโยงค่าของเซลล์กับรายการดรอปดาวน์ใน Excel (5 วิธี)
• รายการแบบเลื่อนลงตามเงื่อนไขใน Excel (สร้าง จัดเรียง และใช้งาน)
• วิธีการสร้างรายการดรอปดาวน์ขึ้นอยู่กับไดนามิกใน Excel
• วิธีใช้คำสั่ง IF เพื่อสร้างรายการดรอปดาวน์ใน Excel
• VLOOKUP พร้อมรายการดรอปดาวน์ใน Excel