Search code examples
vbalistboxuserform

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


Solution

  • 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