Fill combo box ignoring blanks

[Shannon]

I have code to fill a combo box from a selected range, but it also picks up
any blank cells interspersed in that range. Is there a way to tell it to
ignore blanks? This is my code (OpenWS and Lr were declared earlier):

With Me.cboPO
..Clear
ListItems = OpenWS.Range("D7600").Value
ListItems = Application.WorksheetFunction.Transpose(ListItems)
' convert values to a vertical array
For i = 1 To Lr
.AddItem ListItems(i) ' populate the listbox
Next i
.ListIndex = -1 ' no items selected, set to 0 to select the first item
End With

Thanks for your help!

 

[Tom Ogilvy]

With Me.cboPO
..Clear
ListItems = OpenWS.Range("D7600").Value


For i = 1 To Ubound(ListItems,1)
if len(trim(listItems(i,1))) > 0 then
.AddItem ListItems(i,1) ' populate the listbox
end if
Next i
.ListIndex = -1 ' no items selected, set to 0 to select the first item
End With

 

[Shannon]

Thank you so much! Works woderfully!

With Me.cboPO
.Clear
ListItems = OpenWS.Range("D7600").Value


For i = 1 To Ubound(ListItems,1)
if len(trim(listItems(i,1))) > 0 then
.AddItem ListItems(i,1) ' populate the listbox
end if
Next i
.ListIndex = -1 ' no items selected, set to 0 to select the first item
End With