Zeile löschen wenn 2 bestimmte Spalten leer sind

Bild

Betrifft: Zeile löschen wenn 2 bestimmte Spalten leer sind
von: Klaus
Geschrieben am: 07.09.2015 13:41:56

Hallo!
Ich habe aktuell eine Liste mit über 200.000 Zeilen.
Im Großteil der Zeilen ist in den Spalten A und B (manchmal auch in beiden) ein Inhalt.
Ich möchte nun alle Zeilen löschen, in denen in Spalte A und B nichts steht.
Würde ich das nur mit Spalte A machen, würde mein Code so aussehen:


 'Alle Zeilen löschen, bei denen Spalte A leer ist
    a = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A1:A" & a).Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Nachdem ich aber nur Zeilen löschen möchte, in denen A UND B leer sind, stoße ich an meine Grenzen.
Ich habe probeweise das Range("A1:A" & a) durch Range ("A1:B" & a) ersetzt, was aber leider nicht funktioniert.
Könnt ihr mir bitte helfen?

Bild

Betrifft: AW: Zeile löschen wenn 2 bestimmte Spalten leer
von: Daniel
Geschrieben am: 07.09.2015 13:52:04
Hi
zunächst einmal solltst du deine Excelversion richtig angeben.
Excel 2003 hat maximal 65536 Zeilen, in sofern muss eine deiner Angaben falsch sein.
da mit neuen Excelversionen oft auch neue Funktionen bereit gestellt werden, wäre es schon wichtig zu wissen, was Sache ist.
probier mal:
Intersect(Columns(1).SpecialCells(xlcelltypeblanks).EntireRow, Columns(2).SpecialCells(xlcelltypeblanks).EntireRow).EntireRow.Delete
Hilfreich wäre, die Tabelle vorher nach Spalte A und Spalte B sortiert, so das die zu löschenden Zeilen eine lückenlos zusammenhängenden Zellblock bilden, das vereinfacht Excel die Arbeit beim Löschen.
sollte die Angabe mit den 200.000 Zeilen richtig sein und du ne Excelversion mit 2007 und höher hast, würde auch folgendes gehen:
1. füge in die erste freie Spalte am Tabellenende von Zeile 2 bis zur letzen gefüllten Zeile folgende Formel ein:
=Wenn(Und(A2="";B2="");0;Zeile())
2. Schreibe in die erste Zeile dieser Hilfsspalte die 0
3. wende auf die ganze Tabelle die Funktion DATEN - DATENTOOLS - DUPLIAKTE ENTFERNEN an, mit der Hilfsspalte als Kriterum und der Option "keine Überschrift"
4. lösche die Hilfsspalte wieder
das ist auch sehr schnell, verarbeitet grösste Datenmengen und braucht auch keine Sortierung.
da es ein einfacher Ablauf von Excelanweisungen ist (Formel einfügen, Menüfunktion ausführen) kann man das gut mit dem Recorder aufzeichnen, wenn man es als Code braucht.
Gruß Daniel

Bild

Betrifft: AW: Zeile löschen wenn 2 bestimmte Spalten leer
von: Klaus
Geschrieben am: 07.09.2015 15:31:53
Hallo Daniel!
Erstmal vielen Dank für deine Hilfe.
Tut mir leid wegen der falschen Angabe. Wenn ich schon um Hilfe bitte sollten wenigstens die Angaben stimmen. Meine Officeversion ist 2010, bis vor kurzem hatte ich noch 2003.
Von der Formel =Wenn(Und(A2="";B2="");0;Zeile()) hatte ich bisher noch nie gehört - die hilft wesentlich besser als alles, was ich mir erhofft hatte.
Zusammengefasst sieht mein funktionierender Code am Ende so aus:

Dim AnzZeilen As Long
AnzZeilen = Worksheets("gestrichen").UsedRange.Rows.Count 'Zeilen zählen und die Zahl in  _
Variable schreiben
    Range("D2", Cells(AnzZeilen, 4)).FormulaR1C1 = "=IF(AND(RC[-3]="""",RC[-2]=""""),0,ROW())" ' _
Formel in alle beschrifteten Zeilen eintragen
    Range("A1", Cells(AnzZeilen, 4)).Select
     ActiveSheet.Range("A2", Cells(AnzZeilen, 4)).RemoveDuplicates Columns:=4, Header:= _
        xlNo
    Columns("D:D").Delete


Bild

Betrifft: AW: Zeile löschen wenn 2 bestimmte Spalten leer
von: Daniel
Geschrieben am: 07.09.2015 15:59:31
Hi
ja das RemoveDuplicates ist schon ne klasse Funktion.
allerdings musst du noch eines beachten:
das RemoveDuplikates entfernt hier nicht alle Zellen mit 0 als Wert, sondern die erste Zelle mit 0 bleibt stehen!
um jetzt alle Zeilen aus einem Bereich zu löschen, geht man so vor, dass man die 0 in die Überschriftenzeile der Hilfsspalte schreibt, dann die Tabelle vollständig als Wertebereich angibt (incl. der Überschriftenzeile) und dann aber die Option "keine Überschrift" anwählt.
Denn die erste Zeile mit 0 bleibt immer stehen, und auf diese Weise ist das dann die Überschrift und nicht eine Zeile aus den Daten.
Gruß Daniel

Bild

Betrifft: AW: Zeile löschen wenn 2 bestimmte Spalten leer sind
von: UweD
Geschrieben am: 07.09.2015 15:11:28
Hallo
ginge über Filtern...

Sub Leer()
    Dim RR As Double
    Dim Rng
    With ActiveSheet
        Set Rng = .Range("A:B")
        RR = Rng.Cells.SpecialCells(xlCellTypeLastCell).Row 'Letzte Zeile aus A:B
        Rng.AutoFilter
        Rng.AutoFilter Field:=1, Criteria1:="="
        Rng.AutoFilter Field:=2, Criteria1:="="
        .Rows("2:" & RR).Delete Shift:=xlUp
        .AutoFilterMode = False
    End With
    
End Sub

Gruß UweD

Bild

Betrifft: AW: Zeile löschen wenn 2 bestimmte Spalten leer sind
von: Gerd L
Geschrieben am: 07.09.2015 18:07:13
Hallo Klaus!

Range("A:A").SpecialCells(4).Offset(, 1).SpecialCells(4).EntireRow.Delete

Gruß Gerd

Bild

Betrifft: AW: clever :-)
von: Daniel
Geschrieben am: 07.09.2015 18:09:34
clever.

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Zeile löschen wenn 2 bestimmte Spalten leer sind"