如何使下拉式選單去除重覆的選項?

andyhcc

請問...
有無辦法用Column A 當B1 ComboxBox內的來源,並去除重覆的選項?

謝謝

[ 本帖最后由 andyhcc 于 2008-12-10 10:58 编辑 ]

ccwan

1. Sub cc()
   
2.     Dim d As Object, i&, arr
   
3.     arr = Range("a1:a" & [a65536].End(3).Row)
   
4.     Set d = CreateObject("scripting.dictionary")
   
5.     For i = 1 To UBound(arr)
   
6.         d(arr(i, 1)) = ""
   
7.     Next i
   
8.     With [b1].Validation
   
9.         .Delete
   
10.         .Add 3, , , Formula1:=Join(d.keys, ",")
    
11.     End With
    
12. End Sub

复制代码

andyhcc

真快....

感謝幫忙...

蓝桥玄霜

Sub yy()
Dim d, Myr%, i%, Arr, k, Shtlist, x
Set d = CreateObject("Scripting.Dictionary")
Myr = [a65536].End(xlUp).Row
Arr = Range("a1:a" & Myr)
For i = 1 To UBound(Arr)
    x = Arr(i, 1)
    If Not d.exists(x) Then
        d.Add x, 1
    End If
Next i
k = d.keys
For i = 0 To UBound(k)
    Shtlist = Shtlist & k(i) & ","
Next i
With Range("b1").Validation
    .Delete         '删除之前有效性设置
    .Add Type:=xlValidateList, Formula1:=Shtlist
End With
End Sub