User Form List box, unique records listing

[Guest]

I have a user form with a list box that I would like to show the unique
records from one column in a worksheet.

I've thought about several ways to do this but was wondering if there is a
really easy way using arrays. The unique records would only need to be
available for this list box so I don't need to store them anywhere but the
list will change frequently.

Any help or thoughts would be appreciated.

 

[Guest]

Hi Rominall,

You could use advanced filter and create a tempory unique list somewhere out
of the way on your worksheet and name it. Then set the Rowsource property of
the test box to the named range as per the following code. The unique list
and named range can be deleted when no longer required.

Note that unique copies with Advanced filter can only be done on the same
worksheet.

Sub Macro1()

'NOTE: Set Rowsource property of text box to TxtBoxRng

Dim rngFilter
Dim rngUnique As Range

'Edit column Id to suit your column
'Include column header for advanced filter
With Sheets("Sheet1")
Set rngFilter = Range(.Cells(1, "A"), _
.Cells(.Rows.Count, "A").End(xlUp))
End With

rngFilter.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("Z1"), Unique:=True

'Edit column Id to suit somewhere out of the way
'on your worksheet. Row starts from row 2 so that
'column header not in text box
With Sheets("Sheet1")
Set rngUnique = Range(.Cells(2, "Z"), _
.Cells(.Rows.Count, "Z").End(xlUp))
End With

ActiveWorkbook.Names.Add Name:="TxtBoxRng", _
RefersToR1C1:=rngUnique

UserForm1.Show

End Sub