Combo box the exludes blanks

  • Thread starter Thread starter EP007
  • Start date Start date
E

EP007

Hi

Thank you in advance to anyone who can help.

I'm trying to create a combo box that is linked to a range on a worksheet
that is populated by a vlookup. Depending on what is being looked up blanks
appear in the data range, how do I get the combobox not to show these blanks?

Many thanks

Euan
 
And you're populating the combobox via code?

You could loop through the range and check to see what's in each cell.

I guessed that you were using a combobox in a Userform (in the VBE):

Option Explicit
Private Sub UserForm_Initialize()
Dim myCell As Range
Dim myRng As Range

With Worksheets("sheet1")
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

With Me.ComboBox1
.Clear
.RowSource = ""
For Each myCell In myRng.Cells
If Trim(myCell.Value) = "" Then
'skip it
Else
.AddItem myCell.Value
End If
Next myCell
End With
End Sub
 
Back
Top