Excel VBA Makro zu langsam

[Nightfly09]

Hallo zusammen,

ich habe vor einigen Jahren mit Excel 2016 eine Datei erstellt, die diverse Makros enthält die mit einer Ausnahme bis heute einigermaßen zufriedenstellend funktionieren. Nach dem Wechsel auf Office 365 ist mir aufgefallen, das einige Makros erhelich langsamer laufen als mit der alten Version, was bei den meisten Makros aufgrund der ohnehin kurzen Laufzeit auch nicht ganz so problematisch ist, wirkt sich bei einem Makro, dessen Laufzeit ich vorher schon als grenzwertig angesehen habe so aus, dass es jetzt einige Minuten läuft statt vorher einige Sekunden.
Da einige Minuten Laufzeit für ein regelmäßig ausgeführtes Makro aus meiner Sicht nicht mehr vertretbar ist, habe ich versucht das Makro zu optimieren um etwas mehr Tempo zu machen. Durch die Veränderungen ist das Makro auch schon merklich schneller geworden, jedoch von der früheren Geschwindigkeit noch immer weit entfernt.
Ich hoffe, dass sich hier jemand findet, der mir eventuell ein paar Tipps geben kann, wie sich das Makro weiter beschleunigen lässt, ohne die Anzahl der abgefragten Zeilen reduzieren zu müssen:

Option Explicit

Sub OEE()

Worksheets("Berechnung").Visible = True
Worksheets("Berechnung").Activate
DoEvents

With Application
  .ScreenUpdating = False
  .Calculation = xlCalculationManual
  .EnableEvents = False
End With

Dim ZQ  As Long
Dim ZZ  As Long
  
With Sheets("OEE")
 For ZQ = 1 To 78522
  If Sheets("Aufzeichnung").Cells(ZQ, 11) > 0 Then
   ZZ = ZZ + 1
   .Cells(ZZ, 1) = Sheets("Aufzeichnung").Cells(ZQ, 1)
   .Cells(ZZ, 2) = Sheets("Aufzeichnung").Cells(ZQ, 9)
   .Cells(ZZ, 3) = Sheets("Aufzeichnung").Cells(ZQ, 10)
   .Cells(ZZ, 4) = Sheets("Aufzeichnung").Cells(ZQ, 11)
   .Cells(ZZ, 5) = Sheets("Aufzeichnung").Cells(ZQ, 12)
   .Cells(ZZ, 6) = Sheets("Aufzeichnung").Cells(ZQ, 13)
   .Cells(ZZ, 7) = Sheets("Aufzeichnung").Cells(ZQ, 14)
   .Cells(ZZ, 8) = Sheets("Aufzeichnung").Cells(ZQ, 15)
   .Cells(ZZ, 9) = Sheets("Aufzeichnung").Cells(ZQ, 16)
   .Cells(ZZ, 10) = Sheets("Aufzeichnung").Cells(ZQ, 17)
   .Cells(ZZ, 11) = Sheets("Aufzeichnung").Cells(ZQ, 18)
  End If
 Next ZQ
End With
 
With Application
  .ScreenUpdating = True
  .Calculation = xlAutomatic
  .EnableEvents = True
End With
 
Call Calculate

Worksheets("XXX").Activate
Worksheets("Berechnung").Visible = xlVeryHidden

End Sub

 

[calippo]

.ScreenUpdating = True

Ich erinnere mich, dass die Aktualisierung während der Berechnung die Geschwindigkeit deutlich reduzieren kann. Wenn der Nutzer nicht nachvollziehen muss, was währenddessen passiert, kann man das ausschalten und nach Abarbeitung der Schleife wieder einschalten.

 

[Stevmon]

Ab Excel 2016 oder 2019 ist so ein Einblendeeffekt auf den Zellen. Wenn .ScreenUpdating = True ist, macht er jedes Mal den Effekt durch, wenn etwas in eine Zelle geschrieben wird und das Makro läuft deutlich länger. Ohne den Code gelesen zu haben würde ich das auch als erstes versuchen.
Ansonsten ist noch die Frage, ob das Makro komplett bis 78522 durchlaufen muss oder ob man vorher schon beenden kann, wenn z. B. ab einer Position nur noch leere Zellen folgen oder so.

 

[TechX]

Befasse Dich mit Range-Objekten - deren Abarbeitung erfolgt flotter.

 

[Scientist]

Screenupdating ist fuer die Berechnung aus. Damit passt doch alles ...

Du kopierst 11 Zellen einzeln. Dabei kannst du die Spalten 9 bis 18 zu einem zusammenfassen (das meint TechX mit Range-Objekten).
Und wie schon angemerkt wird, solltest du hinterfragen, ob wirklich alle Zellen durchlaufen werden muessen.
Vor allem das wird Zeit kosten.

Such mal bei Google "Excel vba letzte Zeile".

Als alternativer Ansatz:
Daten filtern und die gefilterten Daten kopieren.
oder
Spezialfilter nutzen (filtern und kopieren sind inbegriffen) und die ueberfluessigen Spalten loeschen.

Damit kannst du die Schleife umgehen.

 

[calippo]

Screenupdating ist fuer die Berechnung aus. Damit passt doch alles ...

Ah richtig, wird ausgeschaltet und dann wieder ein, passt.

 

[Nightfly09]

Jetzt bin ich schon so lange hier im Forum und doch immer wieder überrascht wie schnell hier die Antworten kommen. Vielen Dank auf jeden Fall schon mal für alle Antworten.

@calippo: Screenupdating war das erste was ich abgeschaltet habe, bei meinen Versuchen das Makro zu beschleunigen. Über Google bin ich dann auch noch auf CalculationManual & EnableEvents gestoßen, was jetzt auch abgeschaltet wird, so lange das Makro läuft.

@Stevmon: Die Idee erst nach der letzten Zeile zu suchen ist eigentlich sehr gut und ich werde auch versuchen das mal umzusetzen, jedoch ist der Effekt nur temporär, da von Zeit zu Zeit tatsächlich bis zur letzten Zeile etwas zum Kopieren vorhanden ist.

@TechX & @Scientist: Ich hatte eigentlich bewusst Cells und nicht Range benutzt, da ich die Position der Zelle mit Cells in der Schleife leichter anpassen kann und meiner Erfahrung nach Cells schneller arbeitet wenn nur eine Zelle kopiert wird. Die Position der zu kopierenden Spalten hat sich während der Erstellung des Makros dabei mehrfach verändert und obwohl ich alles immer wieder schön sortiert habe ist mir tatsächlich nicht aufgefallen, dass es in der fertigen Datei dann sowohl bei Quelle, wie auch beim Ziel tatsächlich 10 Zellen genau nebeneinander sind die kopiert werden.
Ich komme leider frühestens kommende Woche wieder an einen PC, an dem sowohl die Datei verfügbar, wie auch Office 365 installiert ist, aber über Range 10 Kopiervorgänge zusammenzufassen sollte zumindest theoretisch eine deutlich schnellere Ausführung ermöglichen.

@Scientist: Die Filter so zu setzen, dass sich dann ein zusammenhängender Zellbereich kopieren lässt habe ich schon vor Erstellung des Makros mehrfach versucht. Vielleicht bin ich zu blöd dafür oder habe mich damit einfach nicht genug beschäftigt, aber ich war davon damals so genervt, dass ich es ungern nochmal versuchen möchte

Vielen Dank für die Tipps, ich werde versuchen Range und UsedRange zeitnah in das Makro einzubauen und melde mich auf jeden Fall nach der Umsetzung nochmal.

 

[Scientist]

Die Filter so zu setzen, dass sich dann ein Zusammenhängender Zellbereich kopieren lässt [...]

Aber in diesem Fall gibt es doch nur ein Kriterium mit Zellinhalt > 0?

 

[Nightfly09]

Den Filter zu setzen ist nicht mein Problem, das gefilterte Ergebnis zu kopieren ohne dabei die ausgeblendeten Zeilen mit zu erwischen schon eher.

 

[Scientist]

Dann schau dir den Spezialfilter an.