Hide blanks in userform combobox

[nicolewh]

I have a combobox1 that has a row source for a table called "List" column called "List on Form"

The column "List on Form" has some blank spaces. Is there any way I can have the blank spaces hidden within the dropdown list of the combobox1?

So far I use the userform application Row Source and place in it "List
[List on Form]" but of course there are blank spaces in the userform dropdown list due to the blank spaces in the source.

Any help would be very appreciated.

Nicole

 

[jproffer]

LastRow = Range("A" & Rows.Count).End(xlUp).Row
 ComboBox2.RowSource = "[WBName]Sheet1!A2:A" & LastRow

You could sort (either manually or by code) the blanks to the bottom of your list and set it dynamically at runtime.

 

[dodger7]

is the blanks at the bottom of the list or mixed throughout?
If all the blanks are at the bottom (i.e blank spaces incase you add more data), you can set up a dynamic named range as your list. This way there will be no blanks.

Then have your combobox list source as the dynamic named range

HTH
Jamie

 

[nicolewh]

Sorry but I don't understand this:

LastRow = Range("A" & Rows.Count).End(xlUp).Row
 ComboBox2.RowSource = "[WBName]Sheet1!A2:A" & LastRow

I don't see how this code will hide the blank values in the drop-down list. Is there any way I can do something like this:

Combobox1.RowSource = Table(List
[list on Form]), skipblanks = true

And the blanks are not in the bottom of the list so I guess we can't do the dynamic thing. perhaps I can change the list itself so as to not include any blanks.

Nicole

 

[jproffer]

To use:

LastRow = Range("A" & Rows.Count).End(xlUp).Row
 ComboBox2.RowSource = "[WBName]Sheet1!A2:A" & LastRow

You would have to sort the blanks to the bottom. If that isn't possible it won't work. And I don't think that's a possible use for skipblanks, but I could be wrong.

 

[nicolewh]

Ok - I will just have to filter out the blanks in the list - Thanks!

Nicole

 

[Tom Urtis]

You don't need to filter out anything, here are three options among others.

Option 1

Assuming your worksheet holding the list is named Sheet1 and the list is in column C, then in the userform module:

Private Sub UserForm_Initialize()
Dim cell As Range
For Each cell In Worksheets("Sheet1").Columns(3).SpecialCells(2)
ComboBox1.AddItem cell.Value
Next cell
End Sub

Option 2

Assuming your worksheet holding the list is named Sheet1 and the list is in column C, but you do not want the header (presumably "List on Form" or whatever it is) included in the combobox, then this in the userform module:

Private Sub UserForm_Initialize()
Dim cell As Range
With Worksheets("Sheet1")
For Each cell In .Range("C2:C" & .Cells(Rows.Count, 3).End(xlUp).Row)
If Not IsEmpty(cell) Then ComboBox1.AddItem cell.Value
Next cell
End With
End Sub

Option 3

If your data is the result of formulas then the cells will not really be empty so evaluate based on length:

Private Sub UserForm_Initialize()
Dim cell As Range
With Worksheets("Sheet1")
For Each cell In .Range("C2:C" & .Cells(Rows.Count, 3).End(xlUp).Row)
If len(cell.value) > 0 Then ComboBox1.AddItem cell.Value
Next cell
End With
End Sub

Modify for source sheet name and column holding the data.

 

[nicolewh]

It is possible - thank you.

Nicole

 

[evinx]

You don't need to filter out anything, here are three options among others.
Option 3

If your data is the result of formulas then the cells will not really be empty so evaluate based on length:

Private Sub UserForm_Initialize()
Dim cell As Range
With Worksheets("Sheet1")
For Each cell In .Range("C2:C" & .Cells(Rows.Count, 3).End(xlUp).Row)
If len(cell.value) > 0 Then ComboBox1.AddItem cell.Value
Next cell
End With
End Sub

Modify for source sheet name and column holding the data.

how can i add a extra if
for example

If Cell("C") Value = str2 Then

(dont now the correct code for it)

 

[Tom Urtis]

It looks like you almost answered your own question. Using the same code and range example from 8½ years ago with the same assumptions of the list in column C of the same worksheet and the values as constants, further assuming your reference to str2 is a string type variable (not what is in cell STR2):

Private Sub UserForm_Initialize()
Dim cell As Range
For Each cell In Columns(3).SpecialCells(2)
If cell.value = str2 Then AddItem cell.Value
Next cell
End Sub