AW: viele Dateien vergleichen und doppelte suchen
25.08.2010 15:59:09
Rudi
Hallo,
in O ist der Key?
Sub ttt()
Dim sDatei As String, lngLast As Long
Dim wks As Worksheet, wksZiel As Worksheet
Dim rngDel As Range, rngC As Range
Const sPfad = "c:\test\" 'Suchpfad
Application.ScreenUpdating = False
Set wksZiel = Sheets(1)
wksZiel.Cells.Clear
sDatei = Dir(sPfad & "*.xls")
Do While sDatei ""
Set wks = Workbooks.Open(sPfad & sDatei).Sheets(1)
lngLast = wksZiel.Cells(Rows.Count, 1).End(xlUp).Row
If lngLast > 1 Then lngLast = lngLast + 1
wks.Range("A:A,C:C,D:D,O:O").Copy wksZiel.Cells(lngLast, 1) 'Spalten kopieren
wks.Parent.Close False
With wksZiel
If lngLast > 1 Then
.Rows(lngLast).Delete
.Range(.Cells(Rows.Count, 1).End(xlUp).Offset(, 4), .Cells(lngLast, 5)) = sDatei
Else
.Cells(1, 5) = "Dateiname"
.Range(.Cells(Rows.Count, 1).End(xlUp).Offset(, 4), .Cells(lngLast + 1, 5)) = sDatei
End If
End With
sDatei = Dir
Loop
'Spalte 4 auf Unikate untersuchen und sammeln
With wksZiel
For Each rngC In .Range(.Cells(2, 4), .Cells(Rows.Count, 4).End(xlUp))
If Application.CountIf(.Columns(4), rngC) = 1 Then
If rngDel Is Nothing Then
Set rngDel = rngC
Else
Set rngDel = Union(rngDel, rngC)
End If
End If
Next
End With
'Unikate löschen
If Not rngDel Is Nothing Then rngDel.EntireRow.Delete
End Sub
Gruß
Rudi