Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1360to1364
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

Performance-Probl auch o. Berechnung und Aktualisi

Performance-Probl auch o. Berechnung und Aktualisi
12.05.2014 14:10:19
Peter
Guten Tag
Mit einem Code bearbeite ich einen Hauptbuchoutput, damit ich eine Datenbasis habe, auf dessen Basis ich diverse Abfragen machen kann. Da der Output relativ umfangreich ist (gegen 70'000) Zeilen, braucht der Code 8-9 Minuten zum Ablaufen. Das ist eigentlich ganz ok.
Nun habe ich in die Datei verschiedene Auswertungen eingebaut, womit die Datei etwas grösser (vorallem Formeln) geworden ist.
Nun hat sich die Laufzeit etwa verdreifacht. Das hat mich erstaunt, denn am Angang des Codes schalte ich Berechnungen und Aktualisierungen aus (Aufruf von GetMoreSpeed):
Sub GetMoreSpeed(Optional ByVal Modus As Boolean = True)
'kann nach Abbruch manuell in Direktbereich ausgeschaltet werden mit
'getmorespeed false
Static intCalculation As Integer
intCalculation = -4105
If Modus = True Then intCalculation = Application.Calculation
With Application
.ScreenUpdating = Not Modus
.EnableEvents = Not Modus
.Calculation = IIf(Modus = True, xlManual, intCalculation)
.Cursor = IIf(Modus = True, 2, -4143)
End With
End Sub
Insbesondere dieser Codeteil beansprucht extrem viel Zeit:

Application.StatusBar = "Spalten in Tabelle ""HB_bearbeitet"": Überflüssige Zeilen löschen (1)"
'Abfragen, ob sowohl Kolonne Datum und Saldo leer, dann löschen
With Sheets("HB_bearbeitet")
Intersect(.Range(strhDatKonto).SpecialCells(xlCellTypeBlanks).EntireRow, .Range(strhSaldo).SpecialCells(xlCellTypeBlanks)).EntireRow.Delete
End With

Kann ich noch etwas mehr "ausschalten" oder muss damit leben, dass die Datei langsamer wird, wenn mehr Tabellen vorhanden sind, selbst wenn während dem Laufen des Codes die Berechnungen und Aktualisierungen nicht laufen?
Gruss, Peter

4
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Performance-Probl auch o. Berechnung und Aktualisi
12.05.2014 14:29:11
Daniel
Hi
die Aktionen, die Excel beim Löschen von Zeilen ausführt, kann man leider nicht ausschalten.
um das Löschnen von Zeilen zu beschleunigen, gibt es folgende Möglichkeiten:
1. sortiere die Tabelle so, daß die zu löschenden Zeilen möglichst als ein Zellblock ohne unterbrechung direkt untereinander stehen.
Wenn die zu löschenden Zeilen einen lückenlos zusammenhängenden Zellblock bilden, dann kann Excel die notwenigen internen Aktionen für den gesamten Zellblock auf einmal ausführen, dh das Löschen der Range: 1:1000 dauert genauso lange wie das Löschen der Range 1:1, das löschen der unterbrochenen Range 1:1,3:3 hingegen dauert doppelt so lange.
2. Lösche die Zeilen über Duplikate Entfernen
Füge eine Hilfsspalte ein mit einer Formel, die alle Zeilen die gelöscht werden sollen mit 0 markiert und alle die stehenbleiben müssen, mit der aktuellen Zeilennummer.
Schreibe in die Überschriftenzeile der Hilfsspalte ebenfalls die 0
Wende dann auf die ganze Tabelle die Excelfunktion DATEN - DATENTOOLS - DUPLIKATE ENTFERNEN an, mit der Hilfsspalte als Kriteriumsspalte
der zeitaufwendige Hintergrundprozess beim Löschen von Zeilen ist meiner Vermutung nach die Aktion:
durchsuche alle geöffneten Excelmappen auf Formeln, die von dieser Löschung betroffen sind und passe deren Zellbezüge entsprechend an.
In der Lösungsvariante 1. erreichst du, dass diese Aktion nur einmalig für alle zu löschenden Zeilen ausgeführt werden muss und nicht für jede einzeln
Bei der Lösungsvariante 2 wird diese Aktion nicht ausgeführt.
Gruß Daniel

Anzeige
AW: Performance-Probl auch o. Berechnung und Aktualisi
12.05.2014 21:44:56
Peter
Hallo Daniel
Vielen Dank für diesen sehr wertvollen Hinweise.
Ich habe mich für die zweite Version entschieden.
Allerdings habe ich mir die Excelfunktion DATEN - DATENTOOLS - DUPLIKATE ENTFERNEN aufzeichnen lassen und führe sie jetzt mit leichter Abänderung auch mit VBA aus.
With Sheets("HB_bearbeitet")
.Range("$A$4:$U" & lngZeilen).RemoveDuplicates Columns:=21, Header:=xlNo
End With
Der Geschwindigkeitsgewinn ist super (und ich frage mich, wie das technisch wo abläuft, dass das so schnell geht und die SpecialCells-Methode so langsam).
Interessanterweise ist in der Kolonne 21 nebst der ersten Null eine weitere Null etwa an 7. Stelle geblieben - konnte nicht nachvollziehen warum.
Deshalb habe ich dann die SpecialCells-Methode darüber laufen lassen und die zweite Null war dann auch weg.
Super.
Nochmals vielen Dank und Gruss, Peter

Anzeige
AW: Performance-Probl auch o. Berechnung und Aktualisi
13.05.2014 09:59:59
Daniel
Hi
was technisch beim Löschen mit SpecialCells anders läuft als beim Löschen mit RemoveDuplicats, kann man - ohne den Excelquellcode zu kennen - nur vermuten.
und ich vermute dass, was ich schon beschrieben habe:
beim Löschen mit Delete prüft Excel, ob anderweitig Formeln von diesen Löschungen betroffen sind und passt die Zellbezüge in diesen Formeln an
(bspw du hast die Formel =Summe(A5:A10), wenn du jetzt die Zeilen 2 und 7 löschst, wird daraus =Summe(A4:A8))
hierbei muss jeder Zellblock seperat betrachtet werden.
bei RemoveDuplicates findet diese Anpassung der Zellbezüge nicht statt, dh diesen Aufwand spart sich Excel.
zu deinem Nullenproblem:
da solltest du mal schauen, obs wirklich die selbe 0 ist, denn Excel unterscheidet hier zwischen der Zahl Null und dem Text, der nur aus der Ziffer "0" besteht.
Gruß Daniel

Anzeige
AW: Performance-Probl auch o. Berechnung und Aktualisi
13.05.2014 10:05:27
Peter
Hallo Daniel
Vielen Dank für die Antwort.
Da meine Abfragen hier über benannte Bereiche laufen, welche sämtliche Datensätze beinhalten, werden die entsprechenden Formeln nicht tangiert, da der Bezug dieser benannten Bereiche automatisch angepasst werden.
Nochmals vielen Dank für deine Hilfe.
Gruss, Peter

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige