Fill combo box ignoring blanks
 

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("D7:D600").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!

Fill combo box ignoring blanks
 

With Me.cboPO
..Clear
ListItems = OpenWS.Range("D7:D600").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

--
Regards,
Tom Ogilvy

"Shannon" wrote:

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("D7:D600").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!

Fill combo box ignoring blanks
 

Thank you so much! Works woderfully!

"Tom Ogilvy" wrote:

With Me.cboPO
.Clear
ListItems = OpenWS.Range("D7:D600").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

--
Regards,
Tom Ogilvy

"Shannon" wrote:

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("D7:D600").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!