eliminate blanks from listbox rowsource

[Jacob]

I am successfully updating the contents of a listbox using the
selection of a combobox. the listbox is populated by means of setting
the rowsource to a named range when the combobox is changed.
unfortunately, the named ranges will sometimes have blanks which I do
not want displayed in the listbox. my current code looks like this:

Private Sub ComboBoxgroupHSS_DropButtonClick()
With UserFormDesign
.ListBoxHSS.RowSource = .ComboBoxgroupHSS.Value
End With
End Sub

Is there a way to eliminate these blanks without altering my code too
much?

Thanks

 

[Nigel]

Rather than assign the named range to the rowsource property, run a loop
against the named range and test for blank / empty cells, then add non-empty
cells into the list control. Also use the comboxbox change event. Not
shown but you should check that the range chosen is valid?


Private Sub ComboBoxgroupHSS_Change()
Dim c As Range
With UserFormDesign
.ListBoxHSS.Clear
For Each c In Range(.ComboBoxgroupHSS.Value)
If Len(Trim(c.Value)) > 0 Then .ListBoxHSS.AddItem c.Value
Next
End With
End Sub

 

[Jacob]

thanks
This makes sense, but I keep getting an error where I have .ListBoxHSS
and I'm not sure why?

 

[Jacob]

I figured out my problem but ran into another. I had to remove the
rowsource that was specified for the listbox. but now I have a problem
because my range was two columns. it is now treating it as one column.