Entfernen von doppelten Werten in Excel-VBA

In diesem Tutorial wird gezeigt, wie man Duplikate mit der RemoveDuplicates-Methode in VBA entfernt.

RemoveDuplicates-Methode

Wenn Daten in ein Excel-Arbeitsblatt importiert oder eingefügt werden, können sie oft doppelte Werte enthalten. Möglicherweise müssen Sie die eingehenden Daten bereinigen und Duplikate entfernen.

Glücklicherweise gibt es eine einfache Methode innerhalb des Range-Objekts von VBA, mit der Sie dies tun können.

Range("A1:C8").RemoveDuplicates Columns:=1, Header:=xlYes

Die Syntax lautet: RemoveDuplicates([Columns],[Header]

• [Columns] – Geben Sie an, welche Spalten auf doppelte Werte geprüft werden. Alle Spalten müssen übereinstimmen, um als Duplikat zu gelten.
• [Header] – Haben die Daten eine Kopfzeile? xlNo (Standard), xlYes, xlYesNoGuess

Technisch gesehen sind beide Parameter optional. Wenn Sie jedoch das Argument Columns nicht angeben, werden keine Duplikate entfernt.

Der Standardwert für Header ist xlNo. Natürlich ist es besser, dieses Argument anzugeben, aber wenn Sie eine Kopfzeile haben, ist es unwahrscheinlich, dass die Kopfzeile als Duplikat erkannt wird.

RemoveDuplicates-Verwendungshinweise

• Bevor Sie die Methode RemoveDuplicates verwenden, müssen Sie einen Bereich angeben, der verwendet werden soll.
• Die Methode RemoveDuplicates entfernt alle Zeilen mit gefundenen Duplikaten, behält aber die Originalzeile mit allen Werten bei.
• Die RemoveDuplicates-Methode funktioniert nur für Spalten und nicht für Zeilen, aber es kann ein VBA-Code geschrieben werden, um diese Situation zu ändern (siehe später).

Beispieldaten für VBA-Beispiele

Um zu zeigen, wie der Beispielcode funktioniert, werden die folgenden Beispieldaten verwendet:

Doppelte Zeilen entfernen

Mit diesem Code werden alle doppelten Zeilen entfernt, die nur auf den Werten in Spalte A basieren:

Sub DuplikateEntfernenBeisp1()
Range("A1:C8").RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

Beachten Sie, dass wir den Bereich „A1:C8“ explizit definiert haben. Stattdessen können Sie den Bereich UsedRange verwenden. UsedRange ermittelt die zuletzt verwendete Zeile und Spalte Ihrer Daten und wendet RemoveDuplicates auf diesen gesamten Bereich an:

Sub DuplikateEntfernen_UsedRange()
ActiveSheet.UsedRange.RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

UsedRange ist unglaublich nützlich, da Sie den Bereich nicht mehr explizit definieren müssen.

Nachdem Sie diesen Code ausgeführt haben, sieht Ihr Arbeitsblatt nun wie folgt aus:

Da nur Spalte A (Spalte 1) angegeben wurde, wurde das Duplikat „Apfel“, das vorher in Zeile 5 gestanden hatte, entfernt. Die Menge (Spalte 2) ist jedoch anders.

Um Duplikate zu entfernen und mehrere Spalten zu vergleichen, können wir diese Spalten mit einer Array-Methode angeben.

Entfernen von Duplikaten durch Vergleich mehrerer Spalten

Sub DuplikateEntfernen_MehrereSpalten()
ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array(1, 2) , Header:=xlYes
End Sub

Das Array weist VBA an, die Daten mit den beiden Spalten 1 und 2 (A und B) zu vergleichen.

Die Spalten im Array müssen nicht in aufeinanderfolgender Reihenfolge sein.

Sub EinfachesBeispiel()
ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array(3, 1) , Header:=xlYes
End Sub

In diesem Beispiel werden die Spalten 1 und 3 für den Vergleich der Duplikate verwendet.

In diesem Codebeispiel werden alle drei Spalten verwendet, um auf Duplikate zu prüfen:

Sub EinfachesBeispiel()
ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array(1, 2, 3) , Header:=xlYes
End Sub

Entfernen von doppelten Zeilen aus einer Tabelle

Die Funktion RemoveDuplicates kann auch auf eine Excel-Tabelle in genau derselben Weise angewendet werden. Die Syntax ist jedoch etwas anders.

Sub EinfachesBeispiel()
ActiveSheet.ListObjects("Tabelle1").DataBodyRange.RemoveDuplicates Columns:=Array(1, 3), _
Header:=xlYes
End Sub

Damit werden die Duplikate in der Tabelle auf der Grundlage der Spalten 1 und 3 (A und C) entfernt. Die Farbformatierung der Tabelle wird jedoch nicht bereinigt und es bleiben farbige Leerzeilen am unteren Rand der Tabelle zurück.

Entfernen von Duplikaten aus Arrays

Wenn Sie doppelte Werte aus einem Array entfernen müssen, können Sie das Array natürlich auch in Excel ausgeben, die RemoveDuplicates-Methode verwenden und das Array erneut importieren. Wir haben jedoch auch eine VBA-Prozedur zum Entfernen von Duplikaten aus einem Array geschrieben.

Entfernen von Duplikaten aus Datenzeilen mit VBA

Die RemoveDuplicates-Methode funktioniert nur für Datenspalten, aber mit ein wenig Querdenken können Sie eine VBA-Prozedur erstellen, die auch Datenzeilen verarbeitet.

Nehmen wir an, dass Ihre Daten auf Ihrem Arbeitsblatt folgendermaßen aussehen:

Sie haben die gleichen Duplikate wie zuvor in den Spalten B und E, aber Sie können sie nicht mit der Methode RemoveDuplicates entfernen.

Die Lösung besteht darin, mit VBA ein zusätzliches Arbeitsblatt zu erstellen, die Daten dorthin zu kopieren, wobei Sie sie in Spalten transponieren, die Duplikate zu entfernen und sie dann zurückzukopieren, wobei Sie sie wieder in Zeilen transponieren.

Sub EinfachesInZeilen()
    'Die Bildschirmaktualisierung und die Warnmeldungen ausschalten - wir wollen, dass der Code reibungslos läuft, ohne dass der Benutzer sieht
    'was vor sich geht
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    'Ein neues Arbeitsblatt hinzufügen
    Sheets.Add After:=ActiveSheet
    'Das neue Arbeitsblatt 'Kopierblatt' nennen
    ActiveSheet.Name = "Kopierblatt"
    'Die Daten aus dem ursprünglichen Arbeitsblatt Kopieren
    Sheets("DataInRows").UsedRange.Copy
    'Das neu erstellte Blatt aktivieren
    Sheets("Kopierblatt").Activate
    'Beim Einfügen werden die Daten transponiert, so dass sie jetzt in Spalten stehen
    ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
   'Die Duplikate für die Spalten 1 und 3 entfernen
    ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array(1, 3), Header _
        :=xlYes
    'Die Daten im ursprünglichen Arbeitsblatt löschen
    Sheets("DataInRows").UsedRange.ClearContents
    'Die Datenspalten aus dem neu erstellten Arbeitsblatt aktivieren
   Sheets("Kopierblatt").UsedRange.Copy
   'Das ursprüngliche Arbeitsblatt aktievieren
    Sheets("DataInRows").Activate
    
   'Beim Einfügen die nicht doppelten Daten transponieren
    ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    'Das Kopierblatt löschen - nicht mehr benötigt
    Sheets("Kopierblatt").Delete
    'Das Originalblatt aktivieren
    Sheets("DataInRows").Activate
    'Bildschirmaktualisierung und Warnungen wieder einschalten
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

In diesem Code wird davon ausgegangen, dass die ursprünglichen Daten in Zeilen auf einem Arbeitsblatt mit der Bezeichnung „DataInRows“ gespeichert sind.

Nach der Ausführung des Codes wird Ihr Arbeitsblatt wie folgt aussehen:

Das Duplikat „Apples“ in Spalte E ist jetzt entfernt worden. Der Benutzer befindet sich wieder in einer sauberen Position, in der keine fremden Arbeitsblätter herumliegen, und der gesamte Vorgang wurde reibungslos und ohne Bildschirmflackern oder Warnmeldungen durchgeführt.