时间戳

首页 > 解决方案 > 我正在尝试创建一个从单元格填充组合框选项的用户表单。我怎么能只从单元格范围中提取唯一值？

excel - 我正在尝试创建一个从单元格填充组合框选项的用户表单。我怎么能只从单元格范围中提取唯一值？

问题描述

这是我到目前为止将单元格中的所有值填充到我的组合框中的内容：

Private Sub UserForm_Initialize()

'Extracts Locations From Cells
Dim ws As Worksheet
Set ws = Worksheets("B")
Dim Location As Range
For Each Location In ws.Range("E7:E100")
    With Me.Combobox
        .AddItem Location.Value
    End With
Next Location

    
End Sub

标签： excelvba

解决方案

我在另一个论坛上找到了答案。

https://www.mrexcel.com/board/threads/vba-multi-column-dictionary-key-into-combobox.863507/

Private Sub UserForm_Activate()
            
            Application.ScreenUpdating = False

Dim rng As Range
Dim Dn As Range
Dim Dic As Object
      
Set rng = Range(Sheets("Subscription").Range("U2"), Sheets("Subscription").Range("U" & Rows.Count).End(xlUp))
    Set Dic = CreateObject("scripting.dictionary")
        Dic.CompareMode = vbTextCompare
For Each Dn In rng
  If Not Dn = vbNullString Then Dic(Dn.Value) = Empty
Next
With UserForm1.ComboBox1
    .Columncount = 2
    .RowSource = ""
    .List = Dic.Keys
    .ListIndex = 0
End With

推荐阅读