Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1532to1536
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Definierte Zeilen löschen

Definierte Zeilen löschen
13.01.2017 13:48:00
Kai
Hallo zusammen,
ich habe mir mit Hilfe eines Makros, das Bereiche transponiert viel Arbeit erspart.
https://www.herber.de/cgi-bin/callthread.pl?index=1534197
Außerdem möchte ich in dem kopierten Bereich, wenn Spalte G 0 oder Leer ist die gesamte Zeile löschen.
Dies betrifft leider ca. 210000 Datensätze.
Den Code den ich dafür verwende, funktioniert, ist aber sehr langsam:
Sub Lösche_alle_Zeilen_ohne_Prozentwert()
Dim i As Long
For i = Cells(Cells.Rows.Count, 1).End(xlUp).Row To 2 Step -1
On Error Resume Next
If Cells(i, 7) = 0 Then Rows(i).Delete
Next
End Sub

Alle Application Events habe ich deaktiviert.
Ich habe gelesen, das ich um diesen zu beschleunigen, z.B. ein X in eine Hilfsspalte z.B. bei mir H schreiben muss, wenn Spalte G 0 oder Leer ist.
Anschließend müsste ich die Daten sortieren und dann löschen.
Leider habe ich noch keinen Hinweis auf entsprechende Codeschnipsel gefunden, die mich auf den Weg zu einer Lösung bringen.
Hat jemand evtl. einen Lösungsansatz für mich? Bin für jede Hilfe dankbar!
Viele Grüße
Kai

5
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Definierte Zeilen löschen
13.01.2017 14:16:51
Kai
Hallo zusammen,
ich habe noch einen Lösungsansatz gefunden, bekomme aber den Transfer nicht hin:
http://www.office-loesung.de/ftopic477095_0_0_asc.php


Sub ZeilenLöschen()
With Tabelle1.UsedRange
With .Columns(.Columns.Count).Offset(2, 1).Resize(.Rows.Count - 2, 1)
.Formula = "=IF(D3=""x"",TRUE,0)"
.Copy
.PasteSpecial xlPasteValues
.EntireRow.Sort .Cells(1), xlAscending, Header:=False
On Error Resume Next
.SpecialCells(xlCellTypeConstants, xlLogical).EntireRow.Delete
On Error GoTo 0
.ClearContents
End With
End With
End Sub
Wenn ich den Code für meine Bedürfnisse anpasse, wird immer Zeile 4 gelöscht.
Kann jemand diesen Code erklären :-)?
Viele Grüße und vielen Dank!
Kai
Anzeige
AW: Definierte Zeilen löschen
13.01.2017 15:31:55
Daniel
Hi
der Code markiert alle Zeilen, die gelöscht werden sollen mit einem WAHR, und alle die stehenbleiben müssen mit einer 0.
das macht er, in dem er eine entsprechende Formel in die erste freie Spalte am Tabellenende einfügt in der Zeile:
  .Formula = "=IF(D3=""x"",TRUE,0)"

dann kann man über das SpecialCells (entspricht "gehe zu - Inhalte - Konstanten - wahrheitswerte) die Zellen mit WAHR gezielt selektieren und dann diese Zellen mit der Option "ganze Zeile" löschen.
vorher wird noch sortiert, damit die zu löschenden Zeilen einen lückenlosen Block bilden, dann geht das löschen schneller.
welche Zeile gelöscht wird, wird in der von mir gezeigten Formel festgelegt.
die Formel ist momentan noch so eingestellt, dass das ganze ab Zeile 3 greift und dass alle Zeilen gelöscht werden bei denen in Spalte D ein "x" steht.
Du brauchst nur diese Formel anpassen, den Rest kannst du lassen (dh nur fast, da in diesem Code ja das Löschen erst ab Zeile 3 beginnt und bei dir ab Zeile 2, daher:
   With Tabelle1.UsedRange
With .Columns(.Columns.Count).Offset(1, 1).Resize(.Rows.Count - 1, 1)
.Formula = "=IF(OR(G2="""",G2=0),TRUE,0)"
das ist übrigens die schnellste Methode, um in großen Datenmengen Zeilen mit Bedingung zu löschen.
entscheidend ist hierbei das Sortieren.
Die Methode kann man auch von Hand gut einsetzen und dürfte bei genügend großer Datenmenge sogar schneller sein als ein Schleifenmakro.
Gruß Daniel
Anzeige
AW: Definierte Zeilen löschen
15.01.2017 21:42:52
Kai
Hallo Daniel,
Vielen Dank für die großartige Hilfe, es klappt wunderbar.
Vor allem war auch die Erläuterung sehr hilfreich, ich habe zunächst den Code nicht ganz verstanden.
Jetzt ist es mit deutlich geworden wie es funktioniert!
Als ich mich am Code versucht habe wurde immer Zeile 4 gelöscht, egal wo das x war.
Nochmals vielen Dank und einen schönen Restsonntag!
Viele Grüße Kai
AW: Definierte Zeilen löschen
13.01.2017 14:38:18
Oberschlumpf
Hi Kai,
ich habe jetzt nur deinen Lösch-Code berücksichtigt.
Hier, versuch mal (ungetestet, da ich keine Bsp-Datei mit 21.000 Zeilen zur Verfügung habe)
ersetz deinen Lösch-Code komplett mit meinem Code:
Sub Lösche_alle_Zeilen_ohne_Prozentwert()
'es wird eine Array-Variable + eine Zähl-Variable für die Inhalte des Arrays benötigt
Dim larCells, lloIdx As Long
'die Array-Variable wird mit dem Inhalt der Spalte G von Zeile 2 bis letzte, benutzte Zeile  _
in SPALTE A gefüllt
larCells = Range("G2:G" & Cells(Cells.Rows.Count, 1).End(xlUp).Row)
'die Array-Variable ist zwei-dimensional
'erster Wert in larCells(1,1)
'zweiter Wert in larCells(2,1)
'dritter Wert in larCells(3,1)
'usw...
'die Array-Variable...
'...enthält in Dimension 1 so viele Einträge, wie es in Spalte G benutzte Zeilen gibt
'...enthält in Dimension 2 nur einen Wert; und zwar den Wert aus Spalte G in der  _
jeweiligen Zeile
'UBound = letzter Wert in Array-Variable in erster Dimension
'mehr zu UBound, LBound, Arrays in der Excel-Hilfe oder Internet
'die For/Next-Schleife beginnt in der letzten Array-Zeile und endet in der ersten Array- _
Zeile
For lloIdx = UBound(larCells, 1) To 1 Step -1
'wenn der aktuelle Array-Wert = 0 oder "", dann...
If larCells(lloIdx, 1) = 0 Or _
larCells(lloIdx, 1) = "" Then
'...lösche die Zeile mit dem 0- oder ""-Wert
'+ 1 bei lloIdx deswegen, weil...
'Array-Einträge beginnen bei 1
'genutzte Zeilen in Spalte G beginnen bei 2
Rows(lloIdx + 1).Delete
End If
Next
End Sub
Hilfts?
Ciao
Thorsten
Anzeige
AW: Definierte Zeilen löschen
13.01.2017 15:16:35
Kai
Hallo Thorsten,
deine Lösung funktioniert ebenso wie meine zuerst genannte, allerdings läuft das Makro dann ewig.
Wenn ich es richtig verstanden habe, teilt Excel das ganze in Bereiche und wenn man unsortiert löscht, gibt es bei 210.000 Zeilen sehr viele Bereiche die gelöscht werden. Was dann sehr lange dauert. Deswegen hab ich den zweiten Lösungsansatz mit der Hilfsspalte erwähnt. Dieser funktioniert händisch wunderbar, allerdings bekomme ich es nicht in ein Makro gebastelt.
Deine Lösung würde auch funktionieren, wenn man dein makro auf die Hilfsspalte abstellt und zuvor
Wenn in Spalte G der Wert der Zelle 0 oder Leer ist in Spalte H ein x macht und dann nach x sortiert und dann dein o.g. Makro ausführt und löscht.
Trotzdem vielen Dank für deinen Lösungsansatz!
Viele Grüße
Kai
Anzeige

301 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige