Get data which is present in the column range of sheet4 but not present in listbox (listbox1) in listbox2

[Lakshmiss]

Am trying to compare a column range ("B2:B500") from sheet with listbox items and display items which are not in listbox. I want to copy the items which are not present in listbox1 but present in column range in sheet4 to another listbox i.e, listbox2. Someone please help me here

I will not be able to share data, but my code is as below which is giving considering only first item in a listbox1

Private Sub ListBox2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)



'find last row

'LastRow = Sheet4.Cells(Rows.Count, 1).End(xlUp).Row



'clear listbox

Me.ListBox2.Clear



'not just one value, but which ever were selected

For X = 0 To ListBox1.ListCount - 1

If Me.ListBox1.Selected(X) = True Then

'it is selected

curval = Me.ListBox1.List(X)

For Y = 2 To 1000

If curval <> Sheet4.Cells(Y, "b") Then

'found a match; populate listbox2

With ListBox2

.AddItem Sheet4.Cells(Y, "b")

.Font.Size = 8

End With

End If

Next Y



End If



Next X



End Sub

 

[DanteAmor]

Hi @Lakshmiss. Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

I assume the listboxes are in a userform.
So I recommend that you use a button for the process instead of using the double click event, since the double click event is not fired if you have the listbox with the multiselect property.

Please, replace your code with the following:

Private Sub CommandButton1_Click()
  Dim dic As Object
  Dim a As Variant
  Dim i As Long
 
  Set dic = CreateObject("Scripting.Dictionary")
  a = Sheet4.Range("B2", Sheet4.Range("B" & Rows.Count).End(3)).Value
  With ListBox1
    For i = 0 To .ListCount - 1
      If .Selected(i) = True Then
        dic(.List(i)) = Empty
      End If
    Next
  End With
 
  ListBox2.Clear
  For i = 1 To UBound(a, 1)
    If Not dic.exists(a(i, 1)) Then
      ListBox2.AddItem a(i, 1)
    End If
  Next
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

​

Note Code Tag:​

In future please use code tags when posting code. How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting.

​

​