Columns and Rows Values

[Todd Huttenstine]

Below is a listbox code that looks in the range and fills
the combobox with the values. Is there a way to add into
the code to ignore cells that = "" (nothing)?

And also is there a code that instead of looking in columns
(like the below code does), can look for values down rows
instead of across columns? For instance in Range A1:A20.

Thanx

Private Sub UserForm_Initialize()
ListBox1.ColumnCount = 1
ListBox1.RowSource = "ab1:ab26"

ListBox1.ControlSource = "E20"
'Place the ListIndex into cell E20
ListBox1.BoundColumn = 0
End Sub

 

[Chip Pearson]

Todd,

To omit the blanks, or to use a row as the source, you'd have to loop
through the range and use AddItem to add the items to the list box. For
example

Private Sub UserForm_Initialize()
Dim Rng As Range
With ListBox1
.ColumnCount = 1
For Each Rng In Range("AB1:AB26").Cells
If Rng.Text <> "" Then
.AddItem Rng.Text
End If
Next Rng
End With
.ControlSource = "E20"
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com