VBA - scripting.dictionary exlude blank
问题描述:
这可能与scripting.dictionary
从范围中排除空白吗? 我正在使用此代码从范围中查找特殊值。我不需要excel公式范围,但在VBA解决方案(如果存在)。VBA - scripting.dictionary exlude blank
有了这个代码,我在列表框总是一个空白项。
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
答
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
添加如果e.Value “” 然后检查如果不.Exists(E)......之前 –
'如果不.Exists(E)和E vbNullString然后。新增E, Nothing'作为@ShaiRado所述 – R3uK
@ R3uK这从你代码工作,但如果范围只有一个值有错误(“应用程序定义或deffined对象错误”) – Nataniell