For, InStr + Delete sehr langsam

Sub Bereinigen() Dim LastRow As Long, Zeile As Long LastRow = Sheets("Übersicht").Cells(Sheets("Übersicht").Rows.Count, "A").End(xlUp).Row For Zeile = LastRow To 2 Step -1 If InStr(Sheets("Übersicht").Cells(Zeile, 2).Value, "\Text A") = 0 _ And InStr(Sheets("Übersicht").Cells(Zeile, 2).Value, "\Text B") = 0 _ And InStr(Sheets("Übersicht").Cells(Zeile, 2).Value, "\Text C\") = 0 _ Then Sheets("Übersicht").Rows(Zeile).Delete Shift:=xlUp End If Next Zeile End Sub

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Sub Bereinigen() Dim LastRow As Long, Zeile As Long Dim rngDel As Range LastRow = Sheets("Übersicht").Cells(Sheets("Übersicht").Rows.Count, "A").End(xlUp).Row For Zeile = LastRow To 2 Step -1 If InStr(Sheets("Übersicht").Cells(Zeile, 2).Value, "\Text A") = 0 _ And InStr(Sheets("Übersicht").Cells(Zeile, 2).Value, "\Text B") = 0 _ And InStr(Sheets("Übersicht").Cells(Zeile, 2).Value, "\Text C\") = 0 _ Then If rngDel Is Nothing Then Set rngDel = Cells(Zeile, 1) Else Set rngDel = Union(rngDel, Cells(Zeile, 1)) End If Next Zeile If Not rngDel Is Nothing Then rngDel.EntireRow.Delete End Sub

Hi
grundsätzlich gilt:
wann immer du in Excel etwas änderst, laufen eine Reihe von Hintergrundprozessen ab.
die wichtigsten wären:
- Bildschirm anpassen
- Prüfen, ob die Änderung für die Ausführung eines Eventmakros relevant ist
- Prüfen, ob in dieser oder einer anderen Datei Formeln gibt, die sich auf diese Zellen beziehen und dann ggf neu berechnet werden müssen
- prüfen, ob es bedingte Formatierungen gibt, die sich auf diese Zellen beziehen und neu berechnet werden müssen
- beim löschen oder einfügen von Zellen prüfen, ob es Formeln gibt die auf den betroffenen Zellbereich referenzieren und dann die Zellbereiche in diesen Formeln anpassen
usw.
das wird bei jeder Änderung ausgeführt.
einen Teil dieser Aufgaben kann man ausschalten (Application.Calculation, Application.EnableEvents, Application.ScreenUptating)
eine andere Methode die sache zu beschleunigen ist, wenn es möglich ist nicht jede Zelle einzeln zu bearbeiten, sondern möglichst gemeinsam im Block mit einem Bearbeitungsschritt.
Dann kann Excel diese Aufgaben für den ganzen Block gemeinsam ausführen.
Im Prinzip so wie beim Einkaufen, wenn du für jeden Artikel den du brauchst, extra in den Laden gehst, dauert das länger, als wenn du einmal in den Laden gehst und dann alle Artikel zuammen kaufst.
auf diesem Ansatz basiert auch die Empfehlung von Rudi.
alledings hat die Sache beim Einfügen oder Löschen einen Haken.
da wird das gemeinschaftliche Löschen oder einfügen nur dann schneller, wenn die zu löschenden Zeilen möglichst wenige, dafür aber große lückenlos zusammenhängende Zellblöcke bilden.
sobald das einzelne Zeilen sind, bringt das Zusammenfassen in einer Rangevariable nur wenig.
oder so forumliert:
Range("1:1000").Delete dauert genauso lange wie Range("1:1").Delete, weil beide Zellbereiche nur einen einzigen lückenlos zusammenhängenden Zellblock enthalten
Range("1:1,3:3") hingegen dauert doppelt so lang, weil es zwei Zellblöcke sind.
der Grund hierfür ist das Anpassen der Formeln, die auf den gelöschten Zellbereich referenzieren.
Das kann Excel immer nur für einen Lückenlosen Block ausführen und muss beim nächsten Block neu anfangen.
dh um das Löschen von Zeilen wirkungsvoll zu beschleunigen, gibt es zwei wege:
a) man sortiert die Tabelle vor dem Löschen so, dass alle zu löschenden Zeilen einen lückenlos zusammenhängenden Zellblock bilden, der dann in einem Schritt gelöschte werden kann.
hierzu kann es erforderlich sein, für die Sortierung die Zeilen in einer Hilfsspalte per Formel zu kennzeichnen (Formeln kann man in einem Schritt für alle Zeilen gleichzeitig einfügen)
b) man kennzeichnet die zu löschenden Zellen mit einer Formel, hierbei bekommen die zu löschenden Zeilen eine 0, die anderen Zeilen die akuelle Zeilennummer (Zeile())
dann wendet man auf die Tabelle die Menüfunktion DATEN - DATENTOOLS - DUPLIKATE ENTFERNEN an, mit der Hilfsspalte als Kriterium.
Hierfür muss die Tabelle nicht sortiert werden.
Das ist sehr schnell, weil beim Duplikate-Entfernen die Anpassung der Zellbezüge in den Formeln nicht stattfindet und Excel dadurch viel Zeit spart.
Gruß Daniel

Beliebteste Forumthreads (12 Monate)

11 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

11
Beiträge zum Forumthread

Beiträge zu diesem Forumthread