UserForm ComboBox Exclude Blanks

[Tuta]

All,

I have a dynamic range serving as a RowSource for a list that populates a ComboBox in a userform.

I only want the user to see the non-blank items in that range.

I am also using the selection to run a application.match off of so I need to retain it to pre-populate some textboxes.

How can I handle this problem?

Private Sub ComboBox1_Change()

Dim x As Variant

x = Application.Match(ComboBox1, Range("SelectionListing"), 0)

    TextBox1.Text = Cells(x, 15).Value
    TextBox2.Text = Cells(x, 16).Value
    TextBox3.Text = Cells(x, 17).Value

End Sub

 

[Domenic]

If your dynamic range is named 'DynRange', and refers to a single column, try the following code that needs to be placed in the module for the userform...

Private Sub UserForm_Initialize()
    Dim Rng As Range
    Dim i As Long
    Me.ComboBox1.RowSource = ""
    Set Rng = Range("DynRange")
    For i = 1 To Rng.Rows.Count
        If Rng(i) <> "" Then
            Me.ComboBox1.AddItem Rng(i)
        End If
    Next i
End Sub

 

[Tuta]

Domenic -- that worked beautifully !

Many thanks!