Combo box the exludes blanks
 

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

Combo box the exludes blanks
 

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


EP007 wrote:

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

Combo box the exludes blanks
 

Do you have any code developed yet?

"EP007" wrote:

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