In diesem Tutorial wird gezeigt, wie Sie Duplikate mit der RemoveDuplicates-Methode in VBA entfernen.
RemoveDuplicates-Methode
Beim Importieren oder Einfügen von Daten in ein Excel-Arbeitsblatt können diese häufig 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.
| 1 | Range("A1:C8").RemoveDuplicates Columns:=1, Header:=xlYes |
Syntax ist:
RemoveDuplicates([Spalten],[Header]
- [Säulen] - Geben Sie an, welche Spalten auf doppelte Werte überprüft werden. Alle Spalten stimmen sehr überein, um als Duplikat betrachtet zu werden.
- [Header] - Haben Daten einen Header? xlNo (Standard), xlYes, xlYesNoGuess
Technisch 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 übereinstimmt.
EntfernenDuplikate Verwendungshinweise
- Bevor Sie die RemoveDuplicates-Methode verwenden, müssen Sie einen zu verwendenden Bereich angeben.
- Die RemoveDuplicates-Methode entfernt alle Zeilen mit gefundenen Duplikaten, behält jedoch die ursprüngliche Zeile mit allen Werten bei.
- Die RemoveDuplicates-Methode funktioniert nur für Spalten und nicht für Zeilen, aber VBA-Code kann geschrieben werden, um diese Situation zu korrigieren (siehe später).
Beispieldaten für VBA-Beispiele
Um zu zeigen, wie der Beispielcode funktioniert, werden die folgenden Beispieldaten verwendet:
Doppelte Zeilen entfernen
Dieser Code entfernt alle doppelten Zeilen nur basierend auf den Werten in Spalte A:
| 123 | Sub RemoveDupsEx1()Range("A1:C8").RemoveDuplicates Columns:=1, Header:=xlYesEnd Sub |
Beachten Sie, dass wir den Bereich „A1:C8“ explizit definiert haben. Stattdessen können Sie den UsedRange verwenden. Der UsedRange ermittelt die zuletzt verwendete Zeile und Spalte Ihrer Daten und wendet RemoveDuplicates auf diesen gesamten Bereich an:
| 123 | Sub RemoveDups_UsedRange()ActiveSheet.UsedRange.RemoveDuplicates Columns:=1, Header:=xlYesEnd Sub |
UsedRange ist unglaublich nützlich, da Sie den Bereich nicht explizit definieren müssen.
Nachdem Sie diesen Code ausgeführt haben, sieht Ihr Arbeitsblatt jetzt so aus:
Beachten Sie, dass das Duplikat „Äpfel“ in Zeile 5 entfernt wurde, da nur Spalte A (Spalte 1) angegeben wurde. 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 beim Vergleich mehrerer Spalten
| 123 | Sub RemoveDups_MultColumns()ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array(1, 2) , Header:=xlYesEnd 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 fortlaufend sein.
| 123 | Sub SimpleExample()ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array(3, 1) , Header:=xlYesEnd Sub |
In diesem Beispiel werden die Spalten 1 und 3 für den Dublettenvergleich verwendet.
In diesem Codebeispiel werden alle drei Spalten verwendet, um auf Duplikate zu prüfen:
| 123 | Sub SimpleExample()ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array(1, 2, 3) , Header:=xlYesEnd Sub |
Entfernen doppelter Zeilen aus einer Tabelle
Genauso können die RemoveDuplicates auch auf eine Excel-Tabelle angewendet werden. Die Syntax ist jedoch etwas anders.
| 1234 | Sub SimpleExample()ActiveSheet.ListObjects("Table1").DataBodyRange.RemoveDuplicates Columns:=Array(1, 3), _Kopfzeile:=xlJaEnd Sub |
Dadurch werden die Duplikate in der Tabelle basierend auf den Spalten 1 und 3 (A und C) entfernt. Es bereinigt jedoch nicht die Farbformatierung der Tabelle, und Sie sehen farbige leere Zeilen am unteren Rand der Tabelle zurück.
Duplikate aus Arrays entfernen
Wenn Sie doppelte Werte aus einem Array entfernen müssen, können Sie Ihr Array natürlich in Excel ausgeben, die RemoveDuplicates-Methode verwenden und das Array erneut importieren.
Wir haben jedoch auch eine VBA-Prozedur geschrieben, um Duplikate aus einem Array zu entfernen.
Entfernen von Duplikaten aus Datenzeilen mit VBA
Die RemoveDuplicates-Methode funktioniert nur bei Datenspalten, aber mit etwas „out of the box“-Denken können Sie eine VBA-Prozedur erstellen, um mit Datenzeilen umzugehen.
Angenommen, Ihre Daten sehen auf Ihrem Arbeitsblatt so aus:
Sie haben die gleichen Duplikate wie zuvor in den Spalten B und E, können sie jedoch nicht mit der RemoveDuplicates-Methode entfernen.
Die Antwort besteht darin, VBA zu verwenden, um ein zusätzliches Arbeitsblatt zu erstellen, die Daten hineinzukopieren und in Spalten zu transponieren, die Duplikate zu entfernen und es dann zurück zu kopieren, um es wieder in Zeilen zu transponieren.
| 12345678910111213141516171819202122232425262728293031323334353637 | Unter DuplicatesInRows()„Bildschirmaktualisierungen und Warnungen deaktivieren – wir möchten, dass der Code reibungslos läuft, ohne dass der Benutzer es sieht'Was ist losApplication.ScreenUpdating = FalseApplication.DisplayAlerts = False'Neues Arbeitsblatt hinzufügenSheets.Add After:=ActiveSheet'Neues Arbeitsblatt 'CopySheet' aufrufenActiveSheet.Name = "CopySheet"'Kopieren Sie die Daten aus dem ursprünglichen ArbeitsblattSheets("DataInRows").UsedRange.Copy'Aktiviere das neu erstellte BlattSheets("CopySheet").Aktivieren'Einfügen transponiere die Daten, sodass sie jetzt in Spalten sindActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _Falsch, Transponieren:=Wahr'Entfernen Sie die Duplikate für die Spalten 1 und 3ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array(1, 3), Header _:=xlJa'Löschen Sie die Daten im ursprünglichen ArbeitsblattSheets("DataInRows").UsedRange.ClearContents'Kopieren Sie die Datenspalten aus dem neu erstellten ArbeitsblattSheets("Copysheet").UsedRange.Copy'Originalblatt aktivierenSheets("DataInRows").Aktivieren'Einfügen, um die nicht doppelten Daten zu transponierenActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _Falsch, Transponieren:=Wahr'Kopierblatt löschen - wird nicht mehr benötigtSheets("Copysheet").Löschen'Originalblatt aktivierenSheets("DataInRows").Aktivieren"Bildschirmaktualisierungen und Warnungen wieder aktivieren"Application.ScreenUpdating = TrueApplication.DisplayAlerts = TrueEnd Sub |
Dieser Code geht davon aus, dass die ursprünglichen Daten in Zeilen in einem Arbeitsblatt namens „DataInRows“ gespeichert sind.
Nach dem Ausführen des Codes sieht Ihr Arbeitsblatt wie folgt aus:
Das Duplikat „Äpfel“ in Spalte E wurde nun entfernt. Der Benutzer befindet sich wieder in einer sauberen Position, ohne dass überflüssige Arbeitsblätter herumhängen, und der gesamte Prozess verlief reibungslos, ohne Bildschirmflimmern oder Warnmeldungen.
