Combo box the exludes blanks

[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

 

[Dave Peterson]

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

 

[JRForm]

Do you have any code developed yet?