时间戳

首页 > 解决方案 > 排除组合框中的重复值

excel - 排除组合框中的重复值

问题描述

有人可以告诉我如何添加代码以排除组合框中的重复值吗？我已经在列中排除了某些具有某些值的行（例如，当工作已经完成时）如果无法在 1 个代码中执行，则在创建组合框列表后删除重复项也是可以的。但为此我也无法弄清楚如何管理。

这是我的组合框列表的代码。它位于 Userform1_initialize 部分内。

Dim LastRow As Long
Dim aCell As Range

Set ws = Sheets("Requests")

With ws

    LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

    For Each aCell In .Range("B3:B" & LastRow)
        'discard rows in dropdown for ease of use
        If aCell.Value <> "" And aCell.Offset(0, 25).Value = "" And aCell.Offset(0, 22).Value <> "on hold" And aCell.Offset(0, 22).Value <> "cancelled" Then
    
            Me.ComboBox2.AddItem aCell.Value

        End If

    Next
End With

标签： excelvbacomboboxduplicatesuserform

解决方案

您可以使用脚本字典（假设您不在 Mac 上）：

Dim dict, ws As Worksheet
Dim LastRow As Long
Dim aCell As Range

Set ws = Sheets("Requests")
Set dict = CreateObject("scripting.dictionary")

With ws

    LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

    For Each aCell In .Range("B3:B" & LastRow)

        'had to guess here since your Q is missing operators ...
        If aCell.Value <> "" And aCell.Offset(0, 25).Value = "" And _
           aCell.Offset(0, 22).Value <> "on hold" And _
           aCell.Offset(0, 22).Value <> "cancelled" Then

            If Not dict.exists(aCell.Value) Then '? new value ?
                Me.ComboBox2.AddItem aCell.Value
                dict(aCell.Value) = True 'add this value
            End If

        End If

    Next
End With

推荐阅读