VBA Express : Excel - Display Only Unique Items on a Userform Listbox

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.