Struggling to check if duplicate rows have been imported from another workbook
VBA Code:
Sub ImportText_no_Duplicates()
' Tester !!!! Tester
' ImportText1 Macro
Dim fileToOpen As Variant
Dim fileFilterPattern As String
Dim wsMaster As Worksheet
Dim wbTextImport As Workbook
Dim dlr As Long
Dim lr As Long
Dim lImpC As Long
Dim lImpR As Long
Dim DictDuplicates As Object
Dim countMatch As Long
Set DictDuplcicates = CreateObject("Scripting.Dictionary")
Application.ScreenUpdating = False
Set wsMaster = ThisWorkbook.Worksheets("Asset Upload Data 2022")
With wsMaster
lr = .Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
dlr = .Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To dlr
DictDuplicates.Add .Cells(i, 20), i
Next i
End With
fileFilterPattern = "Microsoft Excel Workbooks (*.xls*),*.xls*"
fileToOpen = Application.GetOpenFilename(fileFilterPattern)
' open workbook
If fileToOpen = False Then
' input Cancelled
MsgBox "No file Selected."
Else
Workbooks.OpenText _
Filename:=fileToOpen, _
StartRow:=2, _
DataType:=xlDelimited, _
Tab:=True
Set wbTextImport = ActiveWorkbook
With wbTextImport.Worksheets(1)
lImpC = .Cells(1, .Columns.Count).End(xlToLeft).Column
lImpR = .Cells(Rows.Count, 1).End(xlUp).Row
arrData = .Range("A1:M" & lImpR).Value
End With
wbTextImport.Close False
End If
countMatch = 0
' Now you can work on the array
For i = 2 To UBound(arrData) ' I'm assuming the data copied has headers, if not, change 2 for 1
If DictDuplicates.Exists(arrData(i, 1) & """__""" & arrData(i, 2)) Then
'If the concatenated data exists on the dictionary
MsgBox "Duplicates found, please check data you are attempting to copy"
countMatch = countMatch + 1
Exit For
Else
'If it doesn't import worksheet from a2 to last cells with data on this worksheet from lr in c
If countMatch = 0 And i = UBound(arrData) Then
With wbTextImport.Worksheets(1)
.Range("A2", .Cells(lImpR, lImpC)).Copy wsMaster.Range("C" & lr)
End With
End If
End If
Next i
Application.ScreenUpdating = True
'
End Sub
Last edited by a moderator: