View original (French)

Combobox: Do not display empty cell

Solved
linette44 Posted messages 23 Status Membre -  
 Ghostk -
Hello,
I created a combo box with a row source defined: list!B2:B399.
Some cells in the range of the list sheet are not filled (empty). In my combo box, the empty cells therefore appear between certain values.
Is it possible not to display the empty cells in the combo box?
Thank you in advance for your response.
Best regards,
Linette
Configuration: Windows XP Firefox 2.0.0.14

1 réponse

Réponse 1 / 1
linette44 Posted messages 23 Status Membre 3
 
Hello,
I am still looking for a solution.
I understood that if I choose to fill in the list in ROWSOURCE, I can't do anything.
So I found some code (see below) that I entered which allows me to fill the dropdown list of my combobox, but I haven't figured out how to remove the empty rows from my dropdown list.

Private Sub UserForm_Initialize()
With Sheets("Liste")
ComboBox4.List = .Range("b2:b" & .Range("A65536").End(xlUp).Row).Value
End With
End Sub

Does anyone have a solution?

Linette
3
linette44 Posted messages 23 Status Membre 3
 
For those interested, here is a code that works perfectly

Private Sub UserForm_Initialize()
Dim lf As Long 'declares the variable lf (End Line)
Sheets("Liste").Select
lf = Range("B65536").End(xlUp).Row 'defines the variable lf
ComboBox4.Clear 'empties ComboBox4
'fills ComboBox4
For Each cel In Range("B2:B" & lf) 'loops through all cells from A1 to A_lf
'condition: if the cell is not empty, add its content to ComboBox4
If cel.Value <> "" Then ComboBox4.AddItem cel.Value
Next cel 'next cell
End Sub
0
Ghostk
 
This code has served me well, it works perfectly: Thank you!

GK
0