数组到范围验证列表

我正在创建一系列验证列表,如下所示:

Sub CreateValidationList()
Dim myrange as range
Dim c as range
Dim Validationlist as string
Set myrange = range("A1:A10")
Validationlist = ""

For each c in myrange
If c.row = 1 Then
    Validationlist = c.row
Else
    Validationlist = Validationlist & ", " & c.row
End If
    With c
        .Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Validationlist
        .Value = c.row
    End With
Next
End Sub

实际上,我的验证列表要复杂得多,范围要大得多(> 500行)。因此它花费的时间比我希望的要长。

有没有一种方法可以像将数组传递到范围时那样批量创建验证列表:

Sub ArrayToRange()
Dim myarray as variant
Dim myrange as range
Set myrange = range("A1:A10")
Set myarray = myrange

'Do stuff in array

Range("A1:A10") = myarray
End Sub

希望很清楚。

谢谢!

评论