Viel Zeit und Mühe kann Ihnen dieses Makro sparen, wenn Sie aus einer Tabelle doppelte Zeilen löschen wollen. Praktische Anwendungsbeispiele sind z.B. doppelte Kundendatensätze löschen, Postleitzahlenlisten bereinigen oder Adressdaten auf doppelte Einträge prüfen.
Teil 1: Mit unterschiedlichen Varianten die letzte benutzte Zeile oder Spalte ermitteln/
Teil 2: Leere Zeilen in Exceltabellen löschen
Teil 3: Doppelte Zeilen löschen
Teil 4: Spalten und Zeilen in Excel Tabellen ohne großen Aufwand tauschen
Für das kurze Beispiel wird die Spalte A auf doppelte Einträge geprüft und wenn doppelte Einträge festgestellt werden, die jeweilige Zeile komplett gelöscht. Achtung: Sie können die Änderungen eines ausgeführten Makros nicht wieder rückgängig machen. Am Besten speichern Sie vorher die Datei nochmals ab, um ggf. die gespeicherte Datei verwenden zu können, falls das Ergebnis nicht wie gewünscht ist.
Um das Makro zu nutzen zu können, fügen Sie den Excel Makro-Programmcode in ein Modul einfügen:
Sub DoppelteZeilenLöschen()
Dim letzteZeile As Long
Dim Zeile As Long
letzteZeile = Range("A" & Rows.Count).End(xlUp).Row
For Zeile = letzteZeile To 1 Step -1
If WorksheetFunction.CountIf(Range("A1:A" & Zeile), Range("A" & Zeile)) > 1 Then
Rows(Zeile).EntireRow.Delete
End If
Next
End Sub
Anzahl der Zeilen abhängig von der genutzten Excel-Version
Wichtig: Statt Rows.Count kann auch eine Zahl für die Ermittlung der letzten Zeile angegeben werden. Dies ist jedoch nicht zu empfehlen, da die Anzahl der vorhandenen Zeilen abhängig von der genutzter Excel-Version ist. Die angegebene Zeilenzahl 65536 in der Programmzeile “letzteZeile = Range(“A65536″).End(xlUp).Row” bezieht sich auf die Excel-Versionen bis Excel 2003. Ab Excel 2007 gibt es bis zu 1048576 Zeilen. Möchten Sie den VBA-Code für Excel-Versionen ab Excel 2007 nutzen, ändern Sie die Zeile wie folgt ab: “letzteZeile = Range(“1048576″).End(xlUp).Row”. So gehen Sie sicher, dass auch Inhalte nach der Zeile 65536 berücksichtigt werden. Bei älteren Versionen dagegen würde die geänderte Version zu einem Absturz des Makros führen!
Anschließend können Sie Ihn über das Menü “Extras”, “Makro”, “Makros” oder über eine Schaltfläche ausführen. Wie Sie eine Schaltfläche mit einem Makro belegen wird im Beitrag “Erste Schritte” beschrieben.
Alternativ können Sie folgende Beispieldatei verwenden:
Excel Makro: Doppelte Zeilen Löschen
Alternative mit Bordmitteln: Duplikate entfernen
Falls einzig die Entfernung von doppelten Werten innerhalb eines definierten Zellbereichs erfolgen soll, gibt es mit der Funktion „Duplikate entfernen in neueren Office Versionen eine mögliche Alternative zum oben bereitgestellten VBA Code. Die Vorteile dieser Excel Standardfunktion sind die größere Performance bei großen Datenmengen und die Flexibilität bei der Definition was als Dublette angesehen werden soll.
Es gibt nicht nur Vorteile, sondern auch einige Nachteile bei dieser Variante zu beachten, die in einigen Konstellationen gegen eine Nutzung sprechen. Die Funktion hält nicht ganz, was der Tooltip (siehe Abbildung unten) verspricht.
Einschränkungen der Standardfunktion „Duplikate entfernen“
Mit der Standardfunktion „Duplikate entfernen“ wird eben nicht die ganze Zeile gelöscht, sondern jeweils nur die Inhalte in einem zuvor ausgewählten Zellbereich. Nicht markierte und nicht zusammenhängende Bereiche bleiben unverändert stehen.
Abbildung: vorher
Inhalte unterhalb oder rechts neben der Datentabelle bleiben damit gänzlich unberücksichtigt. Zeilen unterhalb der Tabelle verlieren so den Anschluss, wenn die Werte darüber teilweise entfernt werden. Gibt es in der Tabelle eine vollständig leere Spalte, muss aktiv der Bereich vollständig ausgewählt werden. Excel markiert sonst beim Start der Funktion nur den zusammenhängenden Zellbereich zur aktuell aktiven Zelle.
Die außerhalb der Markierung liegenden Tabellenbereiche, bleiben bei der Entfernung von Duplikaten immer unberücksichtigt. Falls eine Abhängigkeit zum bereinigten Zellbereich besteht, sind diese Daten dann nicht mehr den richtigen Zeilen zugeordnet. Hintergrund: Durch die gelöschten Duplikate verkleinert sich der Zellbereich nach oben (siehe Abbildungen vorher/nachher)! Die Entfernung von Duplikaten über die Excel Standardfunktion kann in diesem Fall jedoch wieder rückgängig gemacht werden.
Abbildung: nachher
Die Formulierung “Doppelte Zeilen aus einem Blatt löschen” im Hinweistext zur Standardfunktion “Duplikate entfernen” ist deshalb missverständlich, da nicht tatsächlich die ganze Zeile gelöscht wird, sondern nur die doppelten Werte innerhalb eines markierten Zellbereichs.
Weiterlesen…
Mit unterschiedlichen Varianten die letzte benutzte Zeile oder Spalte ermitteln/
Spalten und Zeilen in Excel Tabellen ohne großen Aufwand tauschen
Leere Zeilen in Exceltabellen löschen
Hallo
Anstatt Range(“A65536”).End(xlUp).Row
Cells(Rows.Count,1).End(xlUp).Row
verwenden.
Gruss
@Silvan: Stimmt, das ist die bessere Variante. Ursprünglich hatte ich die Variante mit Range(“A65536”) verwendet, um auch VBA Anfängern die Funktionsweise besser näherbringen zu können. Ich habe jedenfalls die Erfahrung gemacht, dass diese Variante besser zu vermitteln ist. Da in der Zwischenzeit aber die überwiegende Anzahl der Office-Anwender mit einer neueren Excel-Version (ab Version 2007) ist diese Variante natürlich inzwischen problematisch. Je nach Version muss mit einer unterschiedlichen letzten möglichen Zeile gearbeitet werden. Daher ist die Variante mit Rows.Count inzwischen absolut zu empfehlen. Bei Gelegenheit werde ich die veröffentlichten Codes noch entsprechend anpassen.
Viele Grüße
Marco
Hallo !
Kann man einen Vergleich von Spalte A und C machen und dann das Original und das Duplikat löschen ? Datei hat eine Überschrift.
Danke
chris
@Chris: Grds. kann man mit VBA so ziemlich alles realisieren. Maßgeschneiderte VBA-Lösungen biete ich auf Anfrage gerne an. https://www.makro-excel.de/erstellungsservice/ Bei Interesse bitte per Email die Datei zusenden und genau definieren, was verglichen werden soll, wo das Ergebnis ausgegeben werden soll. Nicht ganz verstanden habe ich, was genau gelöscht werden soll. Ich kann dann gerne ein individuelles Angebot erstellen.
Moin Marco!
Mit Verlaub:
Sie haben die denkbar schlechteste/langsamste Variante gewählt!
Wozu gibt es Daten, Duplikate entfernen?
@RPP63: Die Funktion Duplikate entfernen ist in der Tat sehr nützlich und nutze ich persönlich auch ziemlich oft. Mit dieser Excel-Standardfunktion wird jedoch nicht eine gesamte Zeile gelöscht, sondern nur ein markierter Zellbereich auf doppelte Werte geprüft und die darin enthaltenen doppelten Werte entfernt.
Den Beitrag habe ich heute ergänzt und die Unterschiede zwischen diesen beiden Varianten erklärt.
Hallo,
vielen Dank für diesen sehr hilfreichen Beitrag! Er funktioniert sehr gut, allerdings würde ich gerne eine kleine Ausnahme einfügen und bitte dabei um Hilfe.
Es sollen doppelte Werte nur gelöscht werden, wenn die Zelle nicht leer ist.
Ich habe eine Liste mit Bestellnummern, die über eine hinterlegte Formel aus einem anderen Excel Dokument geholt werden. In leeren Zellen ist also eine Formel eingetragen, aber noch keine Nummer, da diese erst mit der Zeit in dem anderen Dokument ergänzt werden.
Lass ich nun die Funktion durchlaufen, werden auch die leeren Felder mit hinterlegter Formel gelöscht. Die hinterlegte Formel ist dann weg.
Ist es möglich hier eine Ausnahme einzubeziehen?
Vielen herzlichen Dank und liebe Grüße
Kristina
Frohe Ostern Marco,
im Netz fand ich – ewiger Anfänger – Dein tolles Makro. Auf der rechten Seite der Tabelle, ab G habe ich noch eine Art storyboard, what to do, … untergebracht, sodaß nur alle Mehrfach-Einträge zwischen A6 und H, bis Ende gelöscht werden sollen, in denen in B und C ! Doubletten erscheinen. In
If WorksheetFunction.CountIf(Range(“B6:B” & Zeile), Range(“B” & Zeile)) > 1 And WorksheetFunction.CountIf(Range(“C6:C” & Zeile), Range(“C” & Zeile)) > 1 Then
‘ Rows(Zeile).EntireRow.Delete
Range(Cells(Zeile, 1), Cells(Zeile, 8)).ClearContents
End If
habe ich Dein .Delete durch Range und ClearContents ersetzt, was aber leider Leerzeilen produziert. Magst Du mir sagen, wie ich die verhindern kann ?
Besten Dank Klaus
@Klaus: Dafür kannst Du .ClearContents durch .Delete Shift:=xlUp ersetzen.
Um einzelne Funktionen in VBA umzusetzen ist der Makro-Rekorder eine einfache Möglichkeit sich fehlende Funktionen einfach aufzuzeichnen und das Ergebnis hinterher anzuschauen. Siehe auch: https://www.makro-excel.de/2017/11/08/excel-makros-mit-dem-makro-rekorder-aufzeichnen/
Vielen Dank für die Hilfe, viele Stunden Zeit gespart Dank Dir.
Hallo Marco,
ich hoffe du bist noch aktiv.
Dein Makro hat mir einen ersten Schritt abnehmen und erklären können.
Jetzt möchte ich in meiner Tabelle aber nur dann die Zeile löschen wenn sowohl der Eintrag in “A” als auch in “D” übereinstimmt. bekomme das grade irgendwie nicht hin, kannst du mir dabei evtl helfen?
Beispiel:
Habe Material an verschiedenen Lagerplätzen.
Bisher wenn ich das Makro nutze wird jedes doppelte Material gelöscht, leider halt auch die unterschiedlichen Plätze. Möchte aber wenn es mehrere Orte hat die behalten.
Ich danke
@Marco: Ich würde die Einträge aus Spalte A und D in einer weiteren Spalte zusammenfassen und dann auf diese neue zusammengesetzte Spalte prüfen.