VBA - scripting.dictionary排除空白

Question

是否可以使用scripting.dictionary从范围中排除空白？我使用此代码从range中查找特殊值。我不需要excel公式的范围，但解决方案在VBA (如果存在)。

使用此代码，我在列表框中总是有一个空白项。

Dim v, e

With Sheets("DATA").Range("NAMED_RANGE")
    v = .Value
End With

With CreateObject("scripting.dictionary")
    .CompareMode = 1
    For Each e In v
        If Not .Exists(e) Then .Add e, Nothing
    Next
    If .Count Then Me.TextBox121.List = Application.Transpose(.keys)

    ThisWorkbook.Worksheets("DICTIONARY").Range("B2").Resize(UBound(.keys), 1).Value = _
    Application.Transpose(.keys)
End With

Answer 1

Dim v, e

With Sheets("DATA").Range("NAMED_RANGE")
    If Application.WorksheetFunction.CountA(.Cells) > 1 Then
        v = .Value
    Else
        v = .Cells(1, 1).Value
    End If
End With

With CreateObject("scripting.dictionary")
    .CompareMode = 1
    For Each e In v
        If Not .Exists(e) And e <> vbNullString Then .Add e, Nothing
    Next
    If .Count Then Me.TextBox121.List = Application.Transpose(.Keys)

    If .Count And UBound(.Keys)>0 Then ThisWorkbook.Worksheets("DICTIONARY").Range("B2").Resize(UBound(.Keys), 1).Value = _
        Application.Transpose(.Keys)
End With