Add Items to Combo Box

[Av8tordude]

I want to add items to a combo box however I want to restrict the range to A3:A12. Also, I notice in the current code, when I click the drop down arrow, it displays the empty ranges. Is there a way to not show these ranges if the cells are empty? Can someone assist with this task. Thank you kindly.

Set rng = Range("A3", Range("A3").End(xlDown))For Each cell In rng.Cells
    cbName.AddItem cell.Value
Next cell

 

[My Aswer Is This]

Try this:

This script only looks at range A3 To A12 like you asked for.

Private Sub CommandButton1_Click()
'Modified  11/7/2018  4:59:47 AM  EST
Dim r As Range
ComboBox1.Clear
    For Each r In Range("A3:A12")
        If r.Value <> "" Then ComboBox1.AddItem r.Value
    Next
End Sub

 

[My Aswer Is This]

Now if you wanted the Range to be From A3 to the last value in column A you would use this:

Private Sub CommandButton1_Click()
'Modified  11/7/2018  5:04:08 AM  EST
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Dim r As Range
ComboBox1.Clear
    For Each r In Range("A3:A" & Lastrow)
        If r.Value <> "" Then ComboBox1.AddItem r.Value
    Next
End Sub

 

[Av8tordude]

Works Great!!! Thank you.

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Works Great!!! Thank you.

 

[Av8tordude]

What am I doing wrong?

Dim r As Variant

On Error Resume Next
r = Application.WorksheetFunction.Match(cbName, Range("A3:A12"), 0)


MsgBox Range("e" & r)
Exit Sub

 

[Fluff]

Remove the Worksheetfunction to leave

r = Application.Match(cbName, Range("A3:A12"), 0)