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

Swipez

New Member
Joined
Dec 1, 2005
Messages
8
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
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

gingerafro

Active Member
Joined
Mar 23, 2005
Messages
448
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

New Member
Joined
Dec 1, 2005
Messages
8
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. :confused:

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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,011
Messages
5,569,610
Members
412,282
Latest member
bobHenk
Top