|
|
|
|
|
|
Excel
|
Display Only Unique Items on a Userform Listbox
|
|
Ease of Use
|
Easy
|
Version tested with
|
2002
|
Submitted by:
|
Jacob Hilderbrand
|
Description:
|
This macro shows only unique items in a listbox.
|
Discussion:
|
You have hundreds of rows of data that you use for a listbox. Unfortunately this list has duplicate entries. This macro adds each item to the listbox only once so that you get a unique list.
|
Code:
|
instructions for use
|
Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim UniqueList() As String
Dim x As Long
Dim Rng1 As Range
Dim c As Range
Dim Unique As Boolean
Dim y As Long
Set Rng1 = Sheets("Sheet1").Range("A1:A100")
y = 1
ReDim UniqueList(1 To Rng1.Rows.Count)
For Each c In Rng1
If Not c.Value = vbNullString Then
Unique = True
For x = 1 To y
If UniqueList(x) = c.Text Then
Unique = False
End If
Next
If Unique Then
y = y + 1
Me.ListBox1.AddItem (c.Text)
UniqueList(y) = c.Text
End If
End If
Next
End Sub
|
How to use:
|
- Open Excel.
- Alt + F11 to open the VBE.
- Insert a User Form (Insert | UserForm).
- Add a listbox and a command button to the userform.
- Add the code to the User Form code section.
|
Test the code:
|
- Follow the instructions in "How to use".
- Add data to sheet1 range A1 through A100.
- Run the User Form from the VBE or add a macro with the line (UserForm1.Show) then run the macro
- The listbox will have only unique items.
|
Sample File:
|
Unique List.zip 12.3KB
|
Approved by mdmackillop
|
This entry has been viewed 494 times.
|
|