Microsoft Office Tutorials and References
In Depth Information
Sometimes you’ll need to populate the ComboBox (or ListBox) with items listed in a range that
also contains blank cells. Figure 19-7 shows how horrible that will make the drop-down list
look, if the ComboBox was attempted to be populated with the line of code ComboBox1.List =
figurE 19-7
Much nicer looking is Figure 19-8, which shows empty spaces in its drop-down list even though
empty cells exist among the list of names. The code to do that is shown here, which uses the LEN
function to disregard cells that have no value in them.
Private Sub UserForm_Initialize()
Dim LastRow As Long, cboCell As Range
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
For Each cboCell In Range(“A1:A” & LastRow)
If Len(cboCell) > 0 Then ComboBox1.AddItem cboCell.Value
Next cboCell
End Sub
figurE 19-8
Search JabSto ::

Custom Search