CODEWITHSUNDEEP
vbalistboxuserform
bensasse
bensasse

Reputation: 65

Populating ListBox in Userform with Unique Values on workbook

I'm trying to populate a Listbox with unique values from Column P after populating the workbook through an activebutton. I've written the following syntax but for some reason it is only populating the ListBox with the first cells data but stops there.... Any ideas? I'm sure its something simple I'm missing as I'm new to VBA.

Dim Cellrng As Range
Dim Unique As New Collection
Dim Item As Range

On Error Resume Next
For Each Cellrng In ThisWorkbook.Worksheets("START").Range("P8:P" & lRow)
    Unique.Add Cellrng, CStr(Cell)
Next Cellrng
On Error GoTo 0

For Each Item In Unique
   ListBox1.AddItem Item
Next Item

enter image description hereenter image description here

Upvotes: 1

Views: 1432

Answers (1)

bensasse
bensasse

Reputation: 65

Just needed to change (cell) to (Cellrng)

Dim Cellrng As Range
Dim Unique As New Collection
Dim Item As Range
Dim lRow As Integer

lRow = wkb.Sheets(1).Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).row
    On Error Resume Next
    For Each Cellrng In ThisWorkbook.Worksheets("START").Range("P8:P" & lRow)
        Unique.Add Cellrng, CStr(Cellrng)
    Next Cellrng
    On Error GoTo 0

For Each Item In Unique
   ListBox1.AddItem Item
Next Item

Upvotes: 2

Related Questions