vba combobox list on Userform has blanks

[KDS14589]

This code works fine but my data has blanks, and this code shows them. What can I add to the code to remove them from showing up in the box?



Private Sub UserForm_Initialize()
Dim v, e
With Sheets("domain.cards").Range("c4:c500")
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.Combo_search_status.List = Application.Transpose(.keys)
End With

[georgiboy]

Maybe add a check to see if e is blank:

If Not .exists(e) And e <> vbNullString Then
.Add e, Nothing
End If

[KDS14589]

Maybe add a check to see if e is blank:

If Not .exists(e) And e <> vbNullString Then
.Add e, Nothing
End If

I tried it just now but it didn't work.

I've attached my shared workbook that doesn't have any 'sensitive info.' that I imported those worksheets and UF into. Exceus the code (this is a personal workbook and its a work in process and needs cleaned)
the code I'm working on is at the end of the Card_Data_entry_UF

[snb]

Private Sub UserForm_Initialize()
With Sheets("domaincards")
.Range("c4:c500").AdvancedFilter 2, , .Cells(1, 100), True
.Columns(100).SpecialCells(4).Delete
With .Cells(1, 100).CurrentRegion
ListBox1.List = .Value
.ClearContents
End With
End With
End Sub

[georgiboy]

It needed to be added as below:

Private Sub UserForm_Initialize()
Dim v, e

v = Sheets("domain.cards").Range("C4:C500").Value
With CreateObject("scripting.dictionary")
.comparemode = 1
For Each e In v
If Not .exists(e) And e <> vbNullString Then
.Add e, Nothing
End If
Next
If .Count Then Me.Combo_search_status.List = Application.Transpose(.keys)
End With
End Sub

[snb]

@Georgi


Private Sub UserForm_Initialize()
sn = Sheets("domain.cards").Range("C4:C500")

With CreateObject("scripting.dictionary")
For j = 1 to ubound(sn)
if sn(j,1) <> "" then x0 = .item(sn(j,1))
Next

If .Count Then Combo_search_status.List = .keys
end with
End Sub

[georgiboy]

@Georgi


Private Sub UserForm_Initialize()
sn = Sheets("domain.cards").Range("C4:C500")

With CreateObject("scripting.dictionary")
For j = 1 to ubound(sn)
if sn(j,1) <> "" then x0 = .item(sn(j,1))
Next

If .Count Then Combo_search_status.List = .keys
end with
End Sub

Hi snb,

Looks shorter :clap: just missing one line for me :rofl:

Private Sub UserForm_Initialize()
Dim sn As Variant, j As Long, x0 As String

sn = Sheets("domain.cards").Range("C4:C500")
With CreateObject("scripting.dictionary")
For j = 1 To UBound(sn)
If sn(j, 1) <> "" Then x0 = .Item(sn(j, 1))
Next
If .Count Then Combo_search_status.List = .keys
End With
End Sub

[KDS14589]

It Works.
Thanks georgiboy :hi:

[snb]

@Georgi

2 lines too much: the one you added and the redundant 'Option Explicit'.:hi:
The test ,ifexists is also 100% redundant. see https://www.snb-vba.eu/VBA_Dictionary_en.html#L_5.3

[georgiboy]

and the redundant 'Option Explicit'.:hi: :rofl:

I don't use that - I have the setting changed in the VBE 'Require Variable Declaration' = Checked