Is there a way to remove blank lines from a Combo Box?

[Swipez]

Hi All,

Excel 2000 (Ver 9.07616 SP-3)

Column A = A list of machinery
Column B = Yes / No (can or cannot use machinery)

Column C = =if(B2="yes",A2,"")

Combo Box Range is C2:C200 ,however combo box also picks up all the blank lines.

Is there a way to create the combo box so it only displays the usable machinery?

Many thanks in advance

 

[gingerafro]

Apologies for the delay - hope you come back to try this out!

Private Sub ComboBox1_DropButtonClick()
Application.ScreenUpdating = False
If Disabled = False Then
ComboBox1.Clear

Dim rng As Range
Dim cell As Range

Set rng = Range("C1:C200")

With ComboBox1
For Each cell In rng
If cell <> "" Then
.AddItem cell.Value
Else: End If
Next cell
End With
Else
Disabled = False
End If
Application.ScreenUpdating = True
End Sub

 

[Swipez]

Thanks for the Code, I dont know VB, but cut and past it in.

On running the module got a lovely runtime error. Then excel locked up.
Upon reloading it encountered a problem and had to close.

Runtime Error '-2147467259 (80004005)': Unspecified error. Hitting the debug button takes me to VB editor with a Yellow arrow pointing at "ComboBox1.Clear"

Is there another way of doing what i want? Maybe a conditional Combo box?
or 2 linked combo boxes (if possible)?

Thanks again