Hi all
Teaching myself VBA and I have created the below code that pastes 2 columns of data from a listbox2 to an excel worksheet. However this code does not recognize duplicates already in the excel worksheet. Is there a way I can skip an entry if it already is in the excel spreadsheet?
Also I sometimes get an "out of memory" warning when I use the below code. If anyone can see any obvious issues can you please let me know?
Many thanks
Teaching myself VBA and I have created the below code that pastes 2 columns of data from a listbox2 to an excel worksheet. However this code does not recognize duplicates already in the excel worksheet. Is there a way I can skip an entry if it already is in the excel spreadsheet?
Also I sometimes get an "out of memory" warning when I use the below code. If anyone can see any obvious issues can you please let me know?
Many thanks
VBA Code:
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim i As Long
For i = 0 To Me.ListBox2.ListCount - 1
ListBox2.Selected(i) = True
Next
Dim PS As Worksheet
Set PS = Worksheets("Data")
For i = 0 To Me.ListBox2.ListCount - 1
If Me.ListBox2.Selected(i) = True And Me.ListBox2.List(i, 1) <> "" Then
PS.Range("E52").End(xlUp).Offset(1, 0) = Me.ListBox2.List(i, 0)
PS.Range("E52").End(xlUp).Offset(0, 1) = Me.ListBox2.List(i, 1)
Me.ListBox2.Selected(i) = False
End If
Next i
Me.ListBox2.Clear
Application.ScreenUpdating = True
Unload Me
End Sub