VBA to create ComboBox List from values in a column

[chaddres]

I would like to populate my ComboBox with a list of years that appear in Column I. The issue I have is that column I has multiple recurrences of each year and I only want to add each year one time. So, there might be twenty recurrences of 2004, but I only want to add it one time. Then, there might be ten recurrences of 2005. And, so on.

 

[Alan_P]

Hi chaddres,

you could copy the unique year values to another sheet and then use this as the rowsource for your ComboBox... assuming your ComboBox is on a userform (UserForm1) and the sheet with the years on is "1" and sheet "2" is empty you can give this code a try.

Sub UniqueComboBox()

Dim LastRw As Long
Dim LastUnqRw As Long

LastRw = Sheets(1).Range("I" & Rows.Count).End(xlUp).Row 'count of used cells in column I

Sheets(1).Range("I1:I" & LastRw).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("2").Range("A1"), Unique:=True 'copy unique values to sheet 2

LastUnqRw = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row 'count of unique values

UserForm1.ComboBox1.RowSource = Sheets("2").Range("A2:A" & LastUnqRw) 'A2 (assuming A1 is the header) to end of unique values

End Sub

Hope this helps,
Cheers,
Alan.

 

[chaddres]

Could I do a sort by year and then do something like "if b2 <> b1, then add it to the combo box"? Then, I could move down the column (which would be sorted by year) and only add new values. Thoughts?

 

[Alan_P]

yes good idea... add this to the userform code

Private Sub UserForm_Initialize()

LastRw = Range("I" & Rows.Count).End(xlUp).Row

For i = 1 To LastRw 

    If Cells(i, "I").Value <> Cells(i + 1, "I").Value Then 
    
    p = Cells(i, "I").Value 

    UserForm1.ComboBox1.AddItem p
    
    End If
    
Next

End Sub