Cách xóa giá trị trùng lặp khỏi Drop-down List Excel bằng VBA – Hướng dẫn hoàn chỉnh

Bài viết này chỉ cách xóa giá trị trùng lặp khỏi danh sách thả xuống (drop down list) của worksheet trong Excel nhờ VBA, chỉ giữ giá trị duy nhất (unique). Bạn sẽ học trích xuất giá trị duy nhất xuất hiện ít nhất một lần và chính xác một lần.

Giá trị duy nhất trong Drop Down List với Excel VBA (Xem nhanh)

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

Cách giữ giá trị duy nhất trong Drop Down List với Excel VBA

Ở đây có dropdown list ở ô B3 chứa tên một số quốc gia.

Nhưng như bạn thấy, danh sách có tên lặp lại. Germany lặp 3 lần, Italy lặp 2 lần.

Mục tiêu hôm nay: xóa giá trị trùng lặp khỏi dropdown list, chỉ giữ giá trị duy nhất.

1. Viết Macro giữ giá trị duy nhất xuất hiện ít nhất một lần

Trước hết, viết Macro giữ giá trị duy nhất xuất hiện ít nhất một lần trong dropdown list.

Ví dụ với danh sách trên, kết quả Macro là Germany, Italy, France, England.

Mã VBA cho mục đích này:

⧭ Mã 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

⧭ Kết quả:

Chạy mã. Sẽ xóa giá trị trùng lặp khỏi dropdown list ô B3 của sheet đang hoạt động, chỉ giữ giá trị xuất hiện ít nhất một lần.

⧭ Lưu ý:

Đừng quên kích hoạt sheet chứa dropdown list trước khi chạy mã. Cũng thay đổi tham chiếu ô vị trí list theo nhu cầu trước khi chạy.

Đọc thêm: Cách tạo Drop Down List giá trị duy nhất trong Excel (4 phương pháp)

2. Tạo Macro giữ giá trị duy nhất xuất hiện chính xác một lần

Lần này viết Macro giữ giá trị duy nhất xuất hiện chính xác một lần trong dropdown list.

Ví dụ với danh sách trên, kết quả Macro là France, England.

Mã VBA cho mục đích này:

⧭ Mã 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

⧭ Kết quả:

Chạy mã. Sẽ xóa giá trị có bản sao khỏi dropdown list ô B3 sheet đang hoạt động, chỉ giữ giá trị xuất hiện chính xác một lần.

⧭ Lưu ý:

Lại nhắc: kích hoạt sheet chứa dropdown list trước khi chạy. Thay đổi tham chiếu ô vị trí list theo nhu cầu.

Nội dung liên quan: Cách chọn nhiều từ Drop Down List trong Excel (3 cách)

Đọc tương tự:

• Tạo Drop Down Filter trích xuất dữ liệu dựa trên lựa chọn trong Excel
• Cách tạo Excel Drop Down List có màu (2 cách)
• Excel Drop Down List không hoạt động (8 vấn đề và giải pháp)
• Tự động cập nhật Drop Down List trong Excel (3 cách)
• VBA chọn giá trị từ Drop Down List trong Excel (2 phương pháp)

3. Phát triển UserForm đưa giá trị duy nhất vào Drop Down List

Cuối cùng, phát triển UserForm xóa giá trị trùng lặp khỏi dropdown list và chỉ giữ giá trị duy nhất với VBA.

⧪ Bước 1: Mở UserForm

Vào Insert > UserForm trong VBA editor để mở UserForm mới. UserForm1 mới sẽ mở.

⧪ Bước 2: Kéo thả công cụ vào UserForm

Bên cạnh UserForm có Toolbox. Di chuột qua Toolbox kéo 3 Label và 2 ListBox (dưới Label1 và Label3) và 1 TextBox (dưới Label2) như hình.

Cuối cùng kéo CommandButton góc dưới bên phải.

⧪ Bước 3: Viết code cho ListBox1

Double click ListBox1. Private Subprocedure tên ListBox1_Click mở ra. Nhập mã sau:

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

⧪ Bước 4: Viết code cho TextBox1

Double click TextBox1. Private Subprocedure tên TextBox1_Change mở ra. Nhập mã:

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

⧪ Bước 6: Viết code cho CommandButton1

Double click CommandButton1. Private Subprocedure tên CommandButton1_Click mở ra. Nhập mã:

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

⧪ Bước 7: Viết code chạy UserForm

Chèn Module mới từ VBA toolbar và nhập mã sau:

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

⧪ Bước 8: Chạy UserForm (Kết quả cuối cùng)

UserForm đã sẵn sàng. Chạy Macro tên Run_UserForm.

UserForm tải vào worksheet.

Chọn sheet chứa dropdown list. Ở đây là Sheet3.

Nhập tham chiếu ô vị trí list trên sheet. Ở đây là B3.

Cuối cùng chọn At Least Once hoặc Exactly Once. Ở đây chọn At Least Once.

UserForm trông như sau:

Nhấn OK. Giá trị trùng lặp bị xóa khỏi dropdown list tại vị trí nhập theo tiêu chí chọn.

Đọc thêm: Cách tạo Drop-Down List dựa trên công thức trong Excel (4 cách)

Lưu ý quan trọng

• Bài viết tập trung xóa giá trị trùng lặp khỏi dropdown list. Muốn học tạo dropdown list, hoặc sắp xếp giá trị trong list trùng lặp, đọc bài khác.

Kết luận

Đó là các cách xóa giá trị trùng lặp khỏi dropdown list chỉ giữ giá trị duy nhất nhờ Excel VBA. Có thắc mắc? Hãy hỏi tự do. Đừng quên ghé ExcelDemy để đọc thêm bài viết và cập nhật.

Các bài viết liên quan

• Cách liên kết giá trị ô với Drop Down List trong Excel (5 cách)
• Conditional Drop Down List trong Excel (Tạo, Sắp xếp và Sử dụng)
• Cách tạo Dynamic Dependent Drop Down List trong Excel
• Cách dùng IF Statement tạo Drop-Down List trong Excel
• VLOOKUP với Drop Down List trong Excel