如何创建根据条件创建各种验证列表的宏VBA

Sub CreateValidationList()
    Dim ws As Worksheet
    Dim validationList As String
    Dim lastRow As Long
    
    ' 设置工作表
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' 假设我们要基于列A的值创建验证列表
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' 创建验证列表字符串
    validationList = ""
    For i = 2 To lastRow ' 假设第一行是标题
        validationList = validationList & ws.Cells(i, 1).Value & ","
    Next i
    
    ' 移除最后一个逗号
    validationList = Left(validationList, Len(validationList) - 1)
    
    ' 应用数据验证
    With ws.Range("B1").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=validationList
        .IgnoreBlank = True
        .InCellDropdown = True
    End With
End Sub