Hiding the Blank Cell in a Drop Down List/Combo Box

[Guest]

In the userform i am working on there is a combo box based on a list from a worksheet. This list however has some blank cells in it and these are shown in he combo box as weel. I was wondering whether there was a way to hide these blank cells within the combo box?

 

[Dave Peterson]

Are there blanks because the list has empty cells at the bottom of it?

If yes, you could use a dynamic name that adjusts according to the number of
elements in that list.

See Debra Dalgleish's site for more info:
http://www.contextures.com/xlNames01.html#Dynamic

If the blanks are embedded, I think I'd just add them to dropdown/combobox via
code. But there is a difference between a dropdown (from the Forms toolbar) and
a combobox (from the control toolbox toolbar):

Option Explicit
Sub testme1()

Dim myDD As DropDown
Dim myRng As Range
Dim myCell As Range

With Worksheets("sheet1")
Set myDD = .DropDowns("drop down 1")
myDD.RemoveAllItems
Set myRng = .Range("a1:A20")
For Each myCell In myRng.Cells
If IsEmpty(myCell) Then
'do nothing
Else
myDD.AddItem myCell.Value
End If
Next myCell
End With

End Sub


Sub testme2()

Dim myCombobox As ComboBox
Dim myRng As Range
Dim myCell As Range

With Worksheets("sheet1")
Set myCombobox = .ComboBox1
myCombobox.Clear
Set myRng = .Range("a1:A20")
For Each myCell In myRng.Cells
If IsEmpty(myCell) Then
'do nothing
Else
myCombobox.AddItem myCell.Value
End If
Next myCell

End With

End Sub

 

[Guest]

Thank you Dave your code suggestion was really helpful. However i do have one further question, some of the combo boxes i am working with take their data from columns not rows and although i know how to display the data from columns in combo boxes i am not sure how to join this with hiding the blanks cells from these columns. Do you know how this is possible?