How to remove duplicate item from ListBox in Excel

First of all you need to populate listbox if you don't know then click here to see the tutorial
and then copy the below code and paste it at end of your vba code

You need to define two variables which help you to find out or matched duplicate items in listbox

Dim k As Long

Dim j As Long

Now use With Statement which allows you to perform a series of statements on a specified object without requalifying the name of the object.  

With ListBox1

For k = 0 To .ListCount - 1 ' 

For j = .ListCount - 1 To (k + 1) Step -1

use condition if two value same in same column of ListBox then 

If .List(k) = .List(j) Then

.RemoveItem j

End If

Next j

Next k

End With

The Complete VBA Code for Search and remove duplicate Item are given below..

Option Explicit

Private Sub TextBox1_Change()

Me.TextBox1 = Format(StrConv(Me.TextBox1, vbLowerCase))

Dim sh As Worksheet

Set sh = Sheets("Sheet1")

Dim i As Long

Dim x As Long

Dim p As Long

Me.ListBox1.Clear

'FOR LISTBOX HEADER

With ListBox1

.AddItem "Product Name"

.List(.ListCount - 1, 1) = "HSN Code"

.List(.ListCount - 1, 2) = "Quantity"

.List(.ListCount - 1, 3) = "Rate"

.List(.ListCount - 1, 4) = "GST %"

.List(.ListCount - 1, 5) = "Total"

.Selected(0) = True

End With

For i = 2 To sh.Range("B" & Rows.Count).End(xlUp).Row

For x = 1 To Len(sh.Cells(i, 2))

p = Me.TextBox1.TextLength

If LCase(Mid(sh.Cells(i, 2), x, p)) = Me.TextBox1 And Me.TextBox1 <> "" Then

With Me.ListBox1

.AddItem sh.Cells(i, 2)

.List(.ListCount - 1, 1) = sh.Cells(i, 3)

.List(.ListCount - 1, 2) = sh.Cells(i, 4)

.List(.ListCount - 1, 3) = sh.Cells(i, 5)

.List(.ListCount - 1, 4) = sh.Cells(i, 6)

.List(.ListCount - 1, 5) = sh.Cells(i, 7)

End With

End If

Next x

Next i

'........ Remove duplicate item from listbox

Dim k As Long

Dim j As Long

With lst

For k = 0 To .ListCount - 1

For j = .ListCount - 1 To (k + 1) Step -1

If .List(j) = .List(k) Then

.RemoveItem j

End If

Next

Next

End With

End Sub

Click below download button  to download this file

For Support and Feedback, Please Comments

Click here to goto my channel & subscribe

If you need any question which you are seaching but not found your result, you can share your idea. I can try to solve in my next videos. Thanks for visiting and your support.

Download this File