Office-Blog

Excel: Duplikate in Listen automatisch entfernen per VBA

30.07.2012   Dominik Petri

Wer häufig Daten aus unterschiedlichen Quellen zusammenstellt und anschließend doppelte Datensätze finden und löschen muss, will diese Tätigkeit sicher automatisieren. Die Aufgabe ist damit schneller erledigt und die Ergebnisqualität gleichbleibend hoch.

Wenn der VBA-Entwickler die Fallstricke beim Entfernen von Duplikaten kennt, ist er schon auf dem richtigen Weg. Aber es erwarten ihn noch mehr Überraschungen…

In meinem letzten Blogbeitrag habe ich den Befehl Duplikate entfernen und Excels Spezialfilter zum Finden und Löschen von Dubletten verwendet. Für diese beiden Varianten gibt es in VBA die RemoveDuplicates– bzw. in der AdvancedFilter-Methode des Range-Objekts. Zur Demonstration dieser beiden Methoden verwende ich wieder meine kleine Adressliste.

Variante 1: Die „RemoveDuplicates“-Methode

Ebenso wie der Befehl Duplikate entfernen löscht auch die RemoveDuplicates-Methode die Daten – mit dem Unterschied, dass dieses Löschen nicht(!) rückgängig gemacht werden kann.

Daher kopiere ich zuerst meine Daten vom Blatt „Daten“ ins Blatt „Ausgabe“, um diesen Datenverlust zu verhindern:

Datenverlust vermeiden

Nun kann ich die RemoveDuplicates-Methode auf die Tabelle im Blatt „Ausgabe“ anwenden.

Analog zum Dialogfeld Duplikate entfernen kann ich der Methode mitteilen, welche Spalten zum Prüfen auf doppelte Einträge herangezogen werden sollen und ob meine Daten eine Überschrift tragen.

Sollen alle Spalten geprüft werden, kann ich den Parameter Columns weglassen – das zumindest besagt die Excel-Hilfe. Da die Methode standardmäßig davon ausgeht, dass meine Daten keine Überschrift haben, muss ich in meinem Beispiel den Parameter Header mit xlYes belegen:

Den Parameter Header mit xlYes belegen

Falsche bzw. fehlende Angaben in der Hilfe

Wider Erwarten werden keine Dubletten entfernt und im Blatt „Ausgabe“ stehen alle 15 Datensätze!

Entgegen der Angabe in der Hilfe muss ich den Parameter Columns immer(!) mit den Spalten belegen, die zur Überprüfung berücksichtigt werden. Die Methode erwartet hier ein Array mit den Zahlen der entsprechenden Spalten: In meinem Beispiel die Zahlen 1 bis 6.

Was nicht in der Hilfe steht: Das Array muss null-basiert sein, da es sonst zu einem Laufzeitfehler kommt. Um sicher zu stellen, dass auch bei Option Base 1 das Array null-basiert ist, verwende ich VBA.Array() anstatt Array().

VBA.Array() anstatt Array() verwenden

Nun stimmt auch das Ergebnis im Blatt „Ausgabe“.

Vorsicht bei unterschiedlichen Formaten in einer Spalte

Die RemoveDuplicates-Methode nimmt (genau wie die Schaltfläche Duplikate entfernen) zum Auffinden von Duplikaten den Wert, der in der Zelle angezeigt wird und nicht den Wert, der in der Zelle gespeichert ist.

Daher formatiere ich die Datensätze vor dem Aufruf der Methode, in dem ich das Format der ersten Zeile für alle Zeilen verwende:

Die Datensätze vor dem Aufruf der Methode formatieren

 

 

Und wenn es mal nicht sechs Spalten sind?

Falls die Anzahl der Spalten, aus der ein Datensatz besteht, nicht konstant ist bzw. um den Code flexibel einsetzen zu können, möchte ich kein statisches Array mit den Zahlen 1 bis 6 verwenden, sondern das Array dynamisch während der Laufzeit erstellen:

Das Array dynamisch während der Laufzeit erstellen

Wenn ich dieses Spaltenarray nun der RemoveDuplicates-Methode übergebe, bekomme ich erstaunlicherweise den folgenden Laufzeitfehler:

Unerwarteter Laufzeitfehler

Egal, ob ich das Spaltenarray als Variant deklariere (lt. Hilfe muss der Parameter Columns als Variant übergeben werden) oder einen anderen Datentyp verwende: Der Laufzeitfehler bleibt. Des Rätsels Lösung: Das Spaltenarray muss in Klammern an die Methode übergeben werden!

Den Spaltenarray in Klammern an die Methode übergeben

In VBA können Arrays nur ByRef und nicht ByVal übergeben werden. Aber anscheinend ist es genau das, was die RemoveDuplicates-Methode benötigt, denn das Einklammern der Arrayvariablen führt zu einer Übergabe ByVal.

Variante 2: Die „AdvancedFilter“-Methode

Die AdvancedFilter-Methode des Range-Objekts macht deutlich weniger Probleme. Um eine Liste ohne Duplikate im Blatt „Ausgabe“ zu erstellen, ist lediglich eine einzige Programmzeile nötig:

AdvancedFilter-Methode verwenden

Fazit

Auch bei dem Löschen von Duplikaten mit VBA spricht vieles für die Verwendung des Spezialfilters:

  • Die Daten müssen vorher nicht kopiert werden, um einen Datenverlust zu vermeiden, sondern der Spezialfilter gibt das Ergebnis an einer anderen Stelle aus.
  • Das Format der Daten spielt keine Rolle bei dem Erkennen von doppelten Einträgen.
  • Der Code ist deutlich kürzer und somit weniger fehleranfällig.

Zusatz-Tipp: Mit nur einem Mausklick ins Sicherheitscenter

Wer häufig an Makros arbeitet, wird ziemlich genervt sein, dass man VIERMAL kreuz und quer über den Bildschirm klicken muss, um ins Sicherheitscenter zu gelangen (Datei – Optionen – Sicherheitscenter – Einstellungen für das Sicherheitscenter).

Mit der Tastenfolge Alt, D, O komme ich leider nur bis zu den Optionen.

Doch es geht auch mit nur EINEM Klick!

Das Sicherheitscenter schneller aufrufen

Dazu füge ich lediglich den Befehl Makrosicherheit aus der Registerkarte Entwicklertools zur Schnellzugriffsleiste hinzu. Ab sofort reicht ein Klick und ich bin im Sicherheitscenter in den Einstellungen für Makros.

Das klappt in Excel, Word und PowerPoint. In Outlook funktioniert es zwar auch, aber im Unterschied zu den anderen Anwendungen gibt es in Outlook unter „Befehle nicht im Menüband“ sogar einen Eintrag „Sicherheitscenter“, der mich zur Kategorie „Automatischer Download“ des Sicherheitscenters leitet.

 

Über den Autor

Dominik Petri

- Dipl.-Betriebswirt mit 20 Jahren Berufserfahrung in Großbanken
- Chartered Financial Analyst® und Spezialist für Business Intelligence (BI)
- Zertifizierter Office-Trainer mit den Schwerpunkten Excel und VBA
- Spezialist der ersten Stunde für Power BI mit Excel
- Gründer der offiziellen Microsoft Power BI User Group Frankfurt
- Unterstützt Firmen beim Einführen und Verwenden der BI-Tools von Microsoft
- Projekterfahrener Excel-Berater, VBA-Programmierer für Analysetools
- Autor für Microsoft Press, dpunkt.verlag und »Modernes Reporting mit Excel«

7 Comments so far

Karsten KurzPosted on  6:39 pm - Aug 30, 2016

Hallo Herr Petri,
den Code habe ich von der Webseite abgetippt:
Sub lstObjDefinieren()
Dim LO As ListObject, lngZeileMax As Long
On Error Resume Next

With Tabelle1
lngZeileMax = .Cells(.Rows.Count, 1).End(xlUp).Row
Set LO = .ListObjects.Add(SourceType:=xlSrcRange, _
Source:=Range(„A1:I“ & lngZeileMax), xlListObjecthasheaders:=xlYes)
LO.Name = „Orte“
End With
End Sub
——————————————————————-
Sub DuplicateEntfernen()
Dim SpaltenArray() As Integer, i As Integer

Worksheets(„Tabelle2“).Range(„A1“).CurrentRegion.Clear
Worksheets(„Tabelle1“).ListObjects(„Orte“).Range.Copy _
Destination:=Worksheets(„Tabelle2“).Range(„A1“)

‚Worksheets(„Tabelle2“).ListObjects(1).Range.RemoveDuplicates Columns:=VBA.Array(1, 2, 3, 4, 5, 6, 7, 8, 9), Header:=xlYes
With Worksheets(„Tabelle2“).ListObjects(1)
.DataBodyRange.Resize(1).Copy
.DataBodyRange.PasteSpecial Paste:=xlPasteFormats
End With
Application.CutCopyMode = False

With Worksheets(„Tabelle2“).ListObjects(1)
ReDim SpaltenArray(0 To .ListColumns.Count – 1)
For i = 1 To .ListColumns.Count
SpaltenArray(i – 1) = i
Next i
End With

Worksheets(„Tabelle2“).ListObjects(1).Range.RemoveDuplicates Columns:=(SpaltenArray), Header:=xlYes

End Sub
————————————————————————-
Meine Daten würde ich Ihnen gern schicken, kann aber hier nix anhängen.
Viele Grüße
KarstenKurz

Karsten KurzPosted on  10:09 am - Aug 28, 2016

Hallo,

danke für das Beispiel. Hab ich genau so implementiert.
Aber beim Ausführen des RemoveDuplicates bekomme ich unter Office2010 immer die Fehlermeldung „Laufzeitfehler ’13‘: Typen unverträglich“.
Ich hab kein Ahnung, was hier nicht stimmt.
Bitte um Hilfe.

    Dominik PetriPosted on  1:59 pm - Aug 29, 2016

    Ohne Ihre Daten zu kennen und den VBA-Code zu sehen, lässt sich das Problem nicht lösen.

    ThomasPosted on  2:00 pm - Nov 25, 2016

    Hallo,
    verwende als Datentyp für den Array VARIANT statt Integer, dann sollte es klappen!

      Dominik PetriPosted on  2:56 pm - Nov 25, 2016

      Ja, genau so geht es. Früher ging beides (siehe Blogbeitrag), jetzt muss es scheinbar (wie in der Hilfe beschrieben) VARIANT sein. Hatte es Karsten schon mitgeteilt, aber vergessen auch hier zu posten. Vielen Dank an Thomas!

JudithPosted on  12:53 pm - Mrz 4, 2014

Hi,

ich habe eine Frage zum Dublikate löschen. Bei mir müssen Datensätze, die den gleichen Monat haben stehen bleiben. Sprich wenn ich die Angaben Monat, PLZ, Vorname, Nachname habe, sollen alle Dublikate gelöscht werden, wenn ihre Angaben in den Spalten PLZ, Vorname, Nachname übereinstimmen UND die Monate voneinander abweichen.

Gibt es dafür eine Lösung?

Gerhard DuschaPosted on  6:52 am - Jan 30, 2013

Das hat bei mir Licht angemacht. Danke für das gut nachvollziehbare Beispiel