Combobox List from Column, remove blanks

[SimondsJM]

Hello,

I am trying to populate my combobox1 from Column B starting at B7 and going down but there are blanks in between that I do not want to show up in the list. How can I remove the blanks?

 

[AlphaFrog]

Try something like this...

    Dim c As Range
    
    With Sheets("Sheet1")
        For Each c In .Range("B7", .Range("B" & Rows.Count).End(xlUp))
            If c.Value <> "" Then ComboBox1.AddItem c.Value
        Next c
    End With

 

[SimondsJM]

Where should I add that?

I have tried adding to Combobox_Change()
But nothing.

 

[AlphaFrog]

Where is your combobox located? On a worksheet or UserForm?

 

[SimondsJM]

Combobox is on a userform.

 

[AlphaFrog]

You could put the code in the UserForm_Initialize procedure

Private Sub UserForm_Initialize()

    Dim c As Range
    
    With Sheets("Sheet1")
        For Each c In .Range("B7", .Range("B" & Rows.Count).End(xlUp))
            If c.Value <> "" Then ComboBox1.AddItem c.Value
        Next c
    End With
End Sub

 

[SimondsJM]

I added the code to userform_initialize but is still is not populating the combobox1

 

[AlphaFrog]

Is there any data in cells Sheet1 B7:B?

 

[SimondsJM]

Actually it is sheet 2 but I changed the sheet to match.

And there is data in Column B from B7 down.

 

[AlphaFrog]

Do you get an error? Or is it just a blank ComboBox1 ?

Do you have more than one user form or combobox?

The code is fairly basic. Not much could go wrong. I tested it and it worked for me.