Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Form List box, unique records listing
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Form List box, unique records listing
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 -- Regards, OssieMac "Rominall" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Listing unique values, frequency, and maintaining list order integrity | Excel Worksheet Functions | |||
create a separate list for unique records | Excel Discussion (Misc queries) | |||
Multiple sheets, one user form, and a unique ID | Excel Programming | |||
how to get the number of unique records in a list | Excel Worksheet Functions | |||
Finding unique records from a list. | Excel Discussion (Misc queries) |