Pasting from listbox to spreadsheet - is there a way to avoid duplicate entries?

VBAhelp33

New Member
Joined
Sep 13, 2020
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
Hi all

I am pasting data from 2 columns in a listbox to an excel spreadsheet. Is there a way I can avoid duplicate entries into the worksheet if they are already entered? I would also like to be able to tell the user which entry is a duplicate.

Many thanks

VBA Code:
Private Sub CommandButton4_Click() 'paste selected items in listbox2 to excel worksheet

Application.ScreenUpdating = False

Dim I As Long
For I = 0 To Me.ListBox2.ListCount - 1
  ListBox2.Selected(I) = True
    
Next

Dim rngNext As Range
Dim col As Long

    Set rngNext = Worksheets("data").Range("E" & Rows.Count).End(xlUp).Offset(1)
    For I = 0 To ListBox2.ListCount - 1
    
        If ListBox2.Selected(I) Then
            For col = 0 To ListBox2.ColumnCount - 1
                rngNext.Offset(, col).Value = ListBox2.List(I, col)
            Next col
            
            Set rngNext = rngNext.Offset(1)
            ListBox2.Selected(I) = False
            
        End If
            
    Next I
       

Application.ScreenUpdating = True

BeforeExit:

Set rngNext = Nothing
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,705
Office Version
  1. 2013
Platform
  1. Windows
Something like this
VBA Code:
For col = 0 To ListBox2.ColumnCount - 1
    If Application.CountIf(Columns(col), ListBox2.List(I, col)) = 0 Then
                rngNext.Offset(, col).Value = ListBox2.List(I, col)
    Else
        MsgBox "Duplicate detected in column  " & col
        'Add code here to Exit sub, go to handler, etc.
    End If
Next col
 

VBAhelp33

New Member
Joined
Sep 13, 2020
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
Thanks for your reply
What should Columns be?

VBA Code:
If Application.CountIf(COLUMNS(col), ListBox2.List(I, col)) = 0 Then
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,705
Office Version
  1. 2013
Platform
  1. Windows
You stated you wanted to check for duplicate entries, but did not specify where. The code I suggested would check each column that you are addressing to see if it contains the same value you are about to enter into the cell.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,273
Messages
5,571,253
Members
412,374
Latest member
Nagelgal
Top