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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,131
Messages
5,835,579
Members
430,368
Latest member
User800

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top