如何将唯一值填充到组合框中? [英] How to populate unique values into combobox?

问题描述

我想将唯一值填充到组合框中.

I want to populate unique values into combobox.

我的工作表详细信息

代码:

Private Sub ComboBoxscname_DropButtonClick()
    With Worksheets("A1")
                ComboBoxscname.List = .Range("B2:B" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value
    End With
End Sub

我用黄色突出显示了与"B"列重复的黄色，并且应该仅在组合框中显示一次.

I have highlighted with yellow which are duplicated for column "B" and should be displayed only once in combobox.

我有另一个解决方案，但是在选择特定的工作表名称时出现错误.

Another solution I have but getting error when selecting specific sheet name.

Sub ComboBoxscnameList()

Dim LR As Long
Dim ctrl As Object
'Set ctrl = Sheets("A1").Select

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

ctrl.List() = CreateArray(Range("B2:B" & LR))

End Sub

'creates an array from a given range
'ignores blanks and duplicates

Function CreateArray(r As Range)
    Dim col As New Collection, c As Range, TempArray(), i As Long

    'for each cell in range r
    For Each c In r
        On Error Resume Next
        col.Add c.Value, CStr(c.Value)
        If Err.Number = 0 And Trim(c) <> "" Then
            ReDim Preserve TempArray(i)
            TempArray(i) = c.Value
            i = i + 1
        End If
        Err.Clear
    Next

    CreateArray = TempArray
    Erase TempArray

End Function

Private Sub ComboBoxscname_DropButtonClick()
Call ComboBoxscnameList            
End Sub

推荐答案

从 Column 或 Range 保存唯一值集的最简单方法是使用字典.您遍历B列中的单元格，并检查每个单元格是否已经在 Dictionary 键中，语法为 Dict.Exists("your_parameters").

The easiest way to save a unique set of values from a Column or Range is by using a Dictionary. You loop though your cells in column B, and check if each one is already in the Dictionary keys, the syntax is Dict.Exists("your_parameters").

您可以阅读有关使用 Dictionary 的更多信息这里.

You can read more about using Dictionary HERE.

查看下面的修改后的代码，您要将其添加到您的 UserForm_Initialize()事件中.

Review the modified code below, you want to add it to your UserForm_Initialize() event.

修改后的代码

Modified Code

Private Sub UserForm_Initialize()


Dim i As Long, ArrIndex As Long, LastRow As Long
Dim Dict As Object, Key As Variant
Dim HSNArr() As String

Application.ScreenUpdating = False

' us a Dictionary, and save unique Eco-System as array
Set Dict = CreateObject("Scripting.Dictionary")

With ThisWorkbook.Worksheets("Sheet2") ' <-- modify to your sheet's name
    LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
    ReDim HSNArr(1 To LastRow) ' redim HSN array >> will optimize size later
    ArrIndex = 1

    For i = 2 To LastRow
        If Not Dict.Exists(.Range("B" & i).Value2) And Trim(.Range("B" & i).Value2) <> "" Then  ' make sure not in Dictionary and ignore empty cells
            Dict.Add .Range("B" & i).Value2, .Range("B" & i).Value2 ' add current HSN
            HSNArr(ArrIndex) = .Range("B" & i).Value2
            ArrIndex = ArrIndex + 1
        End If
    Next i
End With
ReDim Preserve HSNArr(1 To ArrIndex - 1) ' resize to populated size of Array

Application.ScreenUpdating = True

With Me.ComboBoxscname
    .Clear ' clear previous combo-box contents
    For i = 1 To UBound(HSNArr) ' loop through array, add each unique HSN to Combo-Box
        .AddItem HSNArr(i)
    Next i

    ' show default value
    .Value = HSNArr(1)
End With

End Sub

这篇关于如何将唯一值填充到组合框中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！