|
楼主 |
发表于 2020-1-30 21:35
|
显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
If t.Row > 1 And t.Column = 3 Then 这个代码会把整个c列做成下拉选项效果,只要C3有就可以,数据有效性不太好,后面加数据的话下拉菜单就不会显示了
Dim sht As Worksheet, arr, d As Object, i%, s, r, r1, s1
Set sht = Sheets("数据有效性")
r = sht.Cells(Rows.Count, 1).End(3).Row
arr = sht.Range("a1:a" & r)
Set d = CreateObject("scripting.dictionary")
For i = 2 To UBound(arr)
s = arr(i, 1)
If Not d.exists(s) Then
d(s) = ""
End If
Next i
With Sheets("本期学员名单").Cells(t.Row, t.Column).Validation '在区域制作筛选下拉菜单
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(d.keys, ",")
End With
Erase arr: Set d = Nothing
End If
End Sub |
|