Giá trị duy nhất trong danh sách thả xuống với VBA trong Excel (Hướng dẫn đầy đủ)

Trong bài viết này, tôi sẽ chỉ cho bạn cách bạn có thể xóa các giá trị trùng lặp khỏi danh sách thả xuống của một trang tính trong Excel với sự trợ giúp của VBA và chỉ giữ lại các giá trị duy nhất. Bạn sẽ học cách trích xuất các 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 danh sách thả xuống 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 danh sách thả xuống với Excel VBA

Tại đây, chúng tôi có một danh sách thả xuống trong ô B3 của một trang tính Excel có chứa tên của một số quốc gia.

Giá trị duy nhất trong danh sách thả xuống với VBA trong Excel (Hướng dẫn đầy đủ)

Nhưng như bạn thấy, trong danh sách, một số cái tên đã được lặp lại. Giống như Đức đã được lặp lại ba lần và Ý đã được lặp lại hai lần.

Mục tiêu của chúng tôi hôm nay là xóa các giá trị trùng lặp khỏi danh sách thả xuống và chỉ giữ lại các giá trị duy nhất.

1. Phát triển Macro để giữ các giá trị duy nhất trong danh sách thả xuống xuất hiện ít nhất một lần

Trước hết, chúng tôi sẽ phát triển Macro để giữ các giá trị duy nhất xuất hiện ít nhất một lần trong danh sách thả xuống.

Ví dụ:đối với danh sách được đề cập ở trên, kết quả của Macro sẽ là Đức, Ý, Pháp, Anh .

VBA mã cho mục đích này sẽ là:

⧭ 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

Giá trị duy nhất trong danh sách thả xuống với VBA trong Excel (Hướng dẫn đầy đủ)

⧭ Đầu ra:

Chạy mã. Nó sẽ xóa các giá trị trùng lặp khỏi danh sách thả xuống của ô B3 của trang tính đang hoạt động và chỉ giữ lại các giá trị xuất hiện ít nhất một lần.

Giá trị duy nhất trong danh sách thả xuống với VBA trong Excel (Hướng dẫn đầy đủ)

⧭ Ghi chú:

Đừng quên kích hoạt trang tính bằng danh sách thả xuống trước khi chạy mã. Ngoài ra, hãy thay đổi tham chiếu ô của vị trí danh sách theo nhu cầu của bạn trước khi chạy mã.

Đọc thêm: Cách tạo danh sách thả xuống có giá trị duy nhất trong Excel (4 phương pháp)

2. Tạo macro để giữ các giá trị duy nhất trong danh sách thả xuống xuất hiện chính xác một lần

Lần này, chúng tôi sẽ phát triển Macro để giữ các giá trị duy nhất xuất hiện chính xác một lần trong danh sách thả xuống.

Ví dụ:đối với danh sách được đề cập ở trên, kết quả của Macro sẽ là Pháp, Anh .

VBA mã cho mục đích này sẽ là:

⧭ 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

Giá trị duy nhất trong danh sách thả xuống với VBA trong Excel (Hướng dẫn đầy đủ)

⧭ Đầu ra:

Chạy mã. Nó sẽ xóa các giá trị trùng lặp khỏi danh sách thả xuống của ô B3 của trang tính đang hoạt động và chỉ giữ lại các giá trị xuất hiện đúng một lần.

Giá trị duy nhất trong danh sách thả xuống với VBA trong Excel (Hướng dẫn đầy đủ)

⧭ Ghi chú:

Một lần nữa, đừng quên kích hoạt trang tính bằng danh sách thả xuống trước khi chạy mã. Ngoài ra, hãy thay đổi tham chiếu ô của vị trí danh sách theo nhu cầu của bạn trước khi chạy mã.

Nội dung có Liên quan: Cách tạo nhiều lựa chọn từ danh sách thả xuống trong Excel (3 cách)

Bài đọc tương tự:

Tạo Bộ lọc Thả xuống để Trích xuất Dữ liệu Dựa trên Lựa chọn trong Excel
Cách tạo danh sách thả xuống Excel với màu (2 cách)
Danh sách thả xuống của Excel không hoạt động (8 vấn đề và giải pháp)
Tự động cập nhật danh sách thả xuống trong Excel (3 cách)
VBA để Chọn Giá trị từ Danh sách Thả xuống trong Excel (2 Phương pháp)

3. Phát triển UserForm để đưa các giá trị duy nhất vào danh sách thả xuống

Cuối cùng, chúng tôi sẽ phát triển UserForm để xóa các giá trị trùng lặp khỏi danh sách thả xuống và chỉ giữ lại các giá trị duy nhất với VBA .

⧪ Bước 1:Mở UserForm

Đi tới Chèn> UserForm trong VBA trình chỉnh sửa để mở UserForm mới . UserForm mới được gọi là UserForm1 sẽ được mở.

Giá trị duy nhất trong danh sách thả xuống với VBA trong Excel (Hướng dẫn đầy đủ)

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

Bên cạnh UserForm , bạn sẽ nhận được Hộp công cụ . Di chuyển con trỏ của bạn qua Hộp công cụ và kéo 3 Nhãn và 2 Hộp danh sách (Trong Nhãn1 và Label3 ) và 1 TextBox (Trong Label2 ) theo cách được thể hiện trong hình.

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

Giá trị duy nhất trong danh sách thả xuống với VBA trong Excel (Hướng dẫn đầy đủ)

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

Nhấp đúp vào ListBox1 . Một Thủ tục con riêng tư được gọi là ListBox1_Click sẽ mở. Nhập mã sau vào đó.

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

Giá trị duy nhất trong danh sách thả xuống với VBA trong Excel (Hướng dẫn đầy đủ)

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

Sau đó, nhấp đúp vào TextBox1 . Một Thủ tục con riêng tư khác được gọi là TextBox1_Change sẽ mở. Nhập mã sau vào đó.

Private Sub TextBox1_Change()

On Error GoTo TB1:

ActiveSheet.Range(UserForm1.TextBox1.Text).Select

Exit Sub

TB1:
    x = 21

End Sub

Giá trị duy nhất trong danh sách thả xuống với VBA trong Excel (Hướng dẫn đầy đủ)

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

Cuối cùng, nhấp đúp vào CommandButton1 . Một Thủ tục con riêng tư được gọi là CommandButton1_Click sẽ mở. Nhập mã sau vào đó.

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

Giá trị duy nhất trong danh sách thả xuống với VBA trong Excel (Hướng dẫn đầy đủ)

⧪ Bước 7:Viết mã để chạy UserForm

Chèn một Mô-đun mới từ thanh công cụ VBA và chèn mã sau vào đó.

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

Giá trị duy nhất trong danh sách thả xuống với VBA trong Excel (Hướng dẫn đầy đủ)

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

UserForm của bạn bây giờ đã sẵn sàng để sử dụng. Chạy Macro được gọi là Run_UserForm .

UserForm sẽ được tải trong trang tính.

Giá trị duy nhất trong danh sách thả xuống với VBA trong Excel (Hướng dẫn đầy đủ)

Chọn trang tính có danh sách thả xuống. Đây là Sheet3 .

Sau đó nhập tham chiếu ô của vị trí danh sách trên trang tính. Đây là B3 .

Cuối cùng, chọn Ít nhất một lần hoặc Chính xác một lần. Ở đây, tôi đã chọn Ít nhất một lần .

Vì vậy, UserForm của tôi trông như thế này:

Giá trị duy nhất trong danh sách thả xuống với VBA trong Excel (Hướng dẫn đầy đủ)

Sau đó nhấp vào OK . Bạn sẽ xóa các giá trị trùng lặp khỏi danh sách thả xuống của vị trí đầu vào theo tiêu chí bạn đã chọn.

Giá trị duy nhất trong danh sách thả xuống với VBA trong Excel (Hướng dẫn đầy đủ)

Đọc thêm: Cách tạo danh sách thả xuống dựa trên công thức trong Excel (4 cách)

Những điều cần nhớ

Trong bài viết này, tôi chỉ tập trung vào việc loại bỏ các giá trị trùng lặp khỏi danh sách thả xuống. Nếu bạn muốn tìm hiểu cách tạo danh sách thả xuống hoặc cách sắp xếp các giá trị trong danh sách trùng lặp, bạn có thể đọc bài viết này.

Kết luận

Vì vậy, đây là những cách để xóa các giá trị trùng lặp khỏi danh sách thả xuống và chỉ giữ lại các giá trị duy nhất phía sau với sự trợ giúp của VBA Excel . Bạn có câu hỏi nào không? Hãy hỏi chúng tôi. Và đừng quên truy cập trang web ExcelDemy của chúng tôi để có thêm bài đăng và cập nhật.

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

Cách liên kết giá trị ô với danh sách thả xuống trong Excel (5 cách)
Danh sách thả xuống có điều kiện trong Excel (Tạo, Sắp xếp và Sử dụng)
Cách tạo danh sách thả xuống phụ thuộc động trong Excel
Cách sử dụng Câu lệnh IF để Tạo Danh sách Thả xuống trong Excel
VLOOKUP với Danh sách thả xuống trong Excel