使用Excel范围

建筑物下拉菜单通常由两部分组成：

• 找到范围，该范围用于值列表
• 将这些值写入以逗号分隔的字符串

下面的代码正是这样做的：

• 首先，它从第1行到第endRow行循环到这些行中的值。将它们写入字符串validationString，并在每个单元格值之后添加逗号
• 最后一个逗号没有用，因此将其与空格validationString = Left(validationString,Len(validationString) - 2)
一起删除。
• validationString被传递到单元格“ A1”的.Validation属性。

Sub TestMe()

    Dim wks As Worksheet: Set wks = Worksheets(1)
    Dim endRow As Long: endRow = LastRow(wks.Name,3)
    Dim validationString As String
    Dim i As Long

    For i = 1 To endRow
        validationString = validationString & wks.Cells(i,"C") & ","
    Next i

    validationString = Left(validationString,Len(validationString) - 2)

    With Worksheets(1).Cells(1,"A").Validation
        .Delete
        .Add Type:=xlValidateList,AlertStyle:=xlValidAlertStop,_
                        Operator:=xlBetween,_
                        Formula1:=validationString
    End With

End Sub


Function LastRow(wsName As String,Optional columnToCheck As Long = 1) As Long
    Dim ws As Worksheet
    Set ws = Worksheets(wsName)
    LastRow = ws.Cells(ws.Rows.Count,columnToCheck).End(xlUp).Row
End Function

不写入范围

这里的“技巧”是从循环中获取数据并将其写入列表，同时循环遍历fsoFolder.Files：

Sub TestMe()

    Dim filePath As String
    filePath = Environ("UserProfile") & "\Desktop\QA"
    Dim fsoLibrary As Object: Set fsoLibrary = CreateObject("Scripting.FileSystemObject")
    Dim fsoFolder As Object: Set fsoFolder = fsoLibrary.GetFolder(filePath)
    Dim fsoFile As Object

    Dim validationString As String
    For Each fsoFile In fsoFolder.Files
        If fsoFile Like "*.txt*" Then
            validationString = validationString & fsoFile.Name & ","
        End If
    Next fsoFile

    validationString = Left(validationString,_
                        Formula1:=validationString
    End With

End Sub