View original (French)

Combobox: Do not display empty cell

Solved
linette44 Posted messages 23 Status Member -  
 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 answer

  1.
    linette44 Posted messages 23 Status Member 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
    1. linette44 Posted messages 23 Status Member 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
    2. Ghostk
       
      This code has served me well, it works perfectly: Thank you!

      GK
      0