再加一句
Private Sub Worksheet_Activate()
Dim Arr
Dim Dic As Object
Set Dic = CreateObject("scripting.dictionary")
With Sheets("Sheet2")
Arr = .Range("A3:F" & .Range("A65536").End(xlUp).Row)
End With
For I = 1 To UBound(Arr)
If Arr(I, 1) <> "" Then Dic(Year(Arr(I, 1))) = ""
Next I
With [A3].Validation
.Delete
If Dic.Count = 0 Then Exit Sub
.Add Type:=xlValidateList, Formula1:=Join(Dic.Keys, ",")
End With
End Sub
空值化成年,就是1899。
因为你取的数据是合并单元格,超过1个数据时包含空格。
可作如下修改:
Private Sub Worksheet_Activate()
Dim Arr
Dim Dic As Object
Set Dic = CreateObject("scripting.dictionary")
With Sheets("Sheet2")
Arr = .Range("A3:F" & .Range("A65536").End(xlUp).Row)
End With
For I = 1 To UBound(Arr)
If Arr(I, 1) <> "" Then Dic(Year(Arr(I, 1))) = ""
Next I
With [A3].Validation
.Delete
.Add Type:=xlValidateList, Formula1:=Join(Dic.Keys, ",")
End With
End Sub
再加一句
Private Sub Worksheet_Activate()
Dim Arr
Dim Dic As Object
Set Dic = CreateObject("scripting.dictionary")
With Sheets("Sheet2")
Arr = .Range("A3:F" & .Range("A65536").End(xlUp).Row)
End With
For I = 1 To UBound(Arr)
If Arr(I, 1) <> "" Then Dic(Year(Arr(I, 1))) = ""
Next I
With [A3].Validation
.Delete
If Dic.Count = 0 Then Exit Sub
.Add Type:=xlValidateList, Formula1:=Join(Dic.Keys, ",")
End With
End Sub