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

Makro beschleunigen

Makro beschleunigen
08.08.2018 15:12:58
Leif
Hallo liebe VBA Experten,
ich habe folgendes Makro im Einsatz, leider dauert der Durchlauf des Zeilen ein und ausblendend ca. 55 Sekunden. Dies ist natürlich für alle Endanwender eine unsägliche Zeit. Hat einer von euch eine Idee, wie ich das Makro bzw. den Durchlauf schneller machen kann oder ggf. dies im Hintergrund läuft und man in den Tabellen weiterarbeiten kann?
Sub NUTD()
Application.ScreenUpdating = False
Dim i As Integer
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name = "4910xx" Or ws.Name = "4910100101" Or ws.Name = "4910107202" Or ws.Name = " _
4910100103" Or ws.Name = "4910100104" Or ws.Name = "4910100105" Or ws.Name = "4910107206" Then
With ws
For i = 23 To 311
If .Cells(i, 19).Value  "hat Wert" Then
.Rows(i).EntireRow.Hidden = True
Else
.Rows(i).EntireRow.Hidden = False
End If
Next i
End With
End If
Next ws
Application.ScreenUpdating = True
End Sub
Danke und Gruß Leif

35
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Makro beschleunigen
08.08.2018 15:22:01
Mister-B
Hallo,
am Anfang des Makros die Kalkulation auf Manuell setzen
Application.Calculation = xlManual
und am Ende wieder auf Automatisch
Application.Calculation = xlAutomatic
Das konnte helfen.
Gruß
Martin
Zwei Alternativen
08.08.2018 15:27:44
lupo1
a) den Hidden-Range mittels Verketten vorbesetzen und dann über Union zuweisen
b) Autofilter
AW: Makro beschleunigen
08.08.2018 15:55:49
ChrisL
Hi
Wenn es nur darum geht die leeren Felder auszublenden.
Sub NUTD()
Dim i As Integer
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
Select Case ws.Name
Case "4910xx", "4910100101", "4910107202", "4910100103", "4910100104", _
"4910100105", "4910107206"
With ws.Range("S23:S311")
.EntireRow.Hidden = False
.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
End With
End Select
Next ws
End Sub
cu
Chris
Anzeige
AW: Makro beschleunigen
08.08.2018 16:12:50
ChrisL
Hi
Noch den Hinweis von Martin aufgenommen. Hatte ich erst weggelassen, da ich die Neuberechnung fälschlicherweise als Ursache ausgeschlossen hatte. Nachdem ich nun mit einer Dummy-Datei mit 1000 Tabellen deinen ursprünglichen Code in unter 1 Sekunde getestet hatte, scheint es mir nun doch plausibel.
Verwendest du vielleicht =TEILERGEBNIS() oder andere volatile Funktionen?
https://www.tabellenexperte.de/excel-im-schneckentempo-volatile-funktionen/
Sub NUTD()
Dim i As Integer
Dim ws As Worksheet
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
Select Case ws.Name
Case "4910xx", "4910100101", "4910107202", "4910100103", "4910100104", _
"4910100105", "4910107206"
With ws.Range("S23:S311")
.EntireRow.Hidden = False
.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
End With
End Select
Next ws
Application.Calculation = xlCalculationAutomatic
End Sub
cu
Chris
Anzeige
Mir wäre neu, dass JEMALS nicht alles bei Auto-
08.08.2018 17:47:05
lupo1
Neuberechnung auch neu berechnet worden wäre - auch vor xl2003.
Für selektives Neuberechnen konnte man doch immer schon Bereiche berechnen. Oder per Reihenfolge durch ein Sub ermitteln.
Anzeige
AW: Mir wäre neu, dass JEMALS nicht alles bei Auto-
09.08.2018 14:53:47
MatthiasG
Hi lupo,
ich meinte das anders. Nämlich, dass alleine das Ein- oder Ausblenden von Zellen zur Neuberechnung führt. Ohne Zellwertänderung. Und zwar nicht nur bei Volatile oder Teilergebnis, sondern immer. Seit Excel 2003.
Gruß Matthias
AW: Makro beschleunigen
09.08.2018 15:03:28
Leif
Hallo Chris,
vielen Dank für deine Hilfe. Ich habe dein Makro angewendet, leider blendet es alle Zeilen zwischen 23-311 ein. Des Weiteren verwende ich einen Blattschutz sodass es bei dem Punkt
.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
auf einen Fehler läuft. Es wäre toll, wenn alle Zeilen in Spalte 19 wo nicht "hat Wert" steht, diese ausgeblendet werden.
Danke und Gruß
Leif
Anzeige
AW: Makro beschleunigen
09.08.2018 15:27:09
ChrisL
Hi
Zwei Varianten... Passwort musst du ggf. noch ergänzen, siehe Antwort
https://www.herber.de/forum/messages/1638467.html
Sub NUTD()
Dim i As Integer
Dim ws As Worksheet
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
Select Case ws.Name
Case "4910xx", "4910100101", "4910107202", "4910100103", "4910100104", _
"4910100105", "4910107206"
ws.Unprotect
With ws.Range("S23:S311")
.EntireRow.Hidden = False
If WorksheetFunction.CountBlank(.Cells) > 0 Then _
.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
End With
ws.Protect
End Select
Next ws
Application.Calculation = xlCalculationAutomatic
End Sub
Sub NUTD()
Dim i As Integer
Dim ws As Worksheet
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
With ws
Select Case .Name
Case "4910xx", "4910100101", "4910107202", "4910100103", "4910100104", _
"4910100105", "4910107206"
.unprotect
For i = 23 To 311
If .Cells(i, 19).Value  "hat Wert" Then
.Rows(i).EntireRow.Hidden = True
Else
.Rows(i).EntireRow.Hidden = False
End If
Next i
.protect
End Select
End With
Next ws
Application.Calculation = xlCalculationAutomatic
End Sub

Anzeige
AW: Makro beschleunigen
09.08.2018 15:45:49
Leif
Hallo Chris,
variante 1 ist super dauert nur 20 Sek, danke. Leider blendet es alle Zeilen ein und keine aus, hast du vielleicht noch eine Idee?
Gruß
Leif
und wie lange dauert Variante 2? owT.
09.08.2018 15:46:44
ChrisL
.
AW: und wie lange dauert Variante 2? owT.
10.08.2018 14:56:04
Leif
Hallo Chris,
Variante 2 dauert ca. 45-60 Sekunden.
Gruß
Leif
AW: und wie lange dauert Variante 2? owT.
10.08.2018 15:04:27
MatthiasG
Hallo Leif,
ich hätte noch was zum Testen:
Hier wird die Zeile nur ausgeblendet, wenn sie eingeblendet ist - und umgekehrt.

Sub NUTD()
Dim i As Integer
Dim ws As Worksheet
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
With ws
Select Case .Name
Case "4910xx", "4910100101", "4910107202", "4910100103", "4910100104", _
"4910100105", "4910107206"
.Unprotect
For i = 23 To 311
If .Cells(i, 19).Value  "hat Wert" Then
RowShow .Rows(i).EntireRow, True
Else
RowShow .Rows(i).EntireRow, False
End If
Next i
.Protect
End Select
End With
Next ws
Application.Calculation = xlCalculationAutomatic
End Sub
Sub RowShow(rng As Range, arg As Boolean)
If arg Then
'Einblenden
If rng.Hidden = False Then rng.Hidden = True
Else
'Ausblenden
If rng.Hidden = True Then rng.Hidden = False
End If
End Sub

Würde mich interessieren, ob das was ändert in der Geschwindigkeit.
Gruß Matthias
Anzeige
Neustart - Ausschluss Neuberechnung als Ursache
10.08.2018 15:59:53
ChrisL
Hi
Bitte den Test von Matthias auch noch machen.
Mittlerweile glaube ich allerdings nicht mehr, dass die Neuberechnung die Ursache ist. Darum geht die Ursachenforschung von vorne los und ich stelle mal ein paar allgemeine Fragen (ohne konkreten Verdacht):
- Ca. Anzahl Blätter, Anzahl Zeilen und generell ungefährer Umfang der Datei?
- Wie/was wird aktualisiert (externe Verknüpfung, Abfragen/Queries, Pivot usw.)?
- Makros, falls ja, welche?
- Verwendete Formeln?
- Wie sieht es im Namensmanager und bei den bedingten Formatierungen aus (Anzahl und ggf. Formeln)?
- Andere Spezialitäten (z.B. Add-Ins, benutzerdefinierte Funktionen (UDF), Diagramme, eingebettete Objekete usw.)?
cu
Chris
Anzeige
AW: Makro beschleunigen
08.08.2018 16:57:49
Leif
Hallo zusammen,
vielen Dank für die vielen Antworten.
Es geht darum, dass die Datei beim öffnen aktualisiert wird und dann durch das Makro eventuelle ausgeblendete Zeilen, wenn in Spalte R der Wert "hat Wert" in einer der Zeilen 23-311 steht diese eingeblendet werden soll oder wenn die Zeile nicht den Wert "hat Wert" hat, ausgeblendet werden soll.
Daher kann ich auf diese Spaltenprüfung nicht verzichten.
Gruß
Leif
AW: Makro beschleunigen
08.08.2018 17:45:37
ChrisL
Noch einmal zum Copy/Pasten
Sub NUTD()
Dim i As Integer
Dim ws As Worksheet
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
With ws
Select Case .Name
Case "4910xx", "4910100101", "4910107202", "4910100103", "4910100104", _
"4910100105", "4910107206"
For i = 23 To 311
If .Cells(i, 19).Value  "hat Wert" Then
.Rows(i).EntireRow.Hidden = True
Else
.Rows(i).EntireRow.Hidden = False
End If
Next i
End Select
End With
Next ws
Application.Calculation = xlCalculationAutomatic
End Sub

Anzeige
AW: Makro beschleunigen
09.08.2018 15:22:56
Leif
Hallo Chris,
vielen Dank für deine Hilfe. Ich habe dein Makro angewendet, leider blendet es alle Zeilen zwischen 23-311 ein. Des Weiteren verwende ich einen Blattschutz sodass es bei dem Punkt
.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
auf einen Fehler läuft. Es wäre toll, wenn alle Zeilen in Spalte 19 wo nicht "hat Wert" steht, diese ausgeblendet werden.
Danke und Gruß
Leif
AW: Makro beschleunigen
08.08.2018 18:01:26
onur

Sub Schaltfläche1_Klicken()
Application.ScreenUpdating = False
Dim i As Integer
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
Select Case ws.Name
Case "4910xx", "4910100101", "4910107202", "4910100103", "4910100104", "4910100105", _
"4910107206"
With ws
For i = 23 To 311
.Rows(i).EntireRow.Hidden = .Cells(i, 19).Value  "hat Wert"
Next i
End With
End Select
Next ws
Application.ScreenUpdating = True
End Sub

Anzeige
Der Bottleneck ist nicht das Verkürzen der boole-
08.08.2018 18:27:41
lupo1
schen Bedingung, sondern das einzelne .Hidden-Zuweisen.
Zeitlich verglichen hast Du die TE-Lösung und Deine sicherlich nicht?
AW: Der Bottleneck ist nicht das Verkürzen der boole-
08.08.2018 19:20:26
onur
Hi Lupo1,
Wo ist das Problem?
Der Code braucht nicht mal eine Sekunde für die komplete Datei.
Wieso überlässt Du nicht einfach Leif die Bewertung?
Gruß
Onur
Nicht gleich so angefasst reagieren, onur ...
08.08.2018 19:32:42
lupo1
... Du möchtest also aus 55 sekündigem
For i = 23 To 311
If .Cells(i, 19).Value "hat Wert" Then
.Rows(i).EntireRow.Hidden = True
Else
.Rows(i).EntireRow.Hidden = False
End If
Next i
folgendes mit 1 Sekunde erreicht haben?
For i = 23 To 311
.Rows(i).EntireRow.Hidden = .Cells(i, 19).Value "hat Wert"
Next i
Das habe ich doch nur bezweifelt. Vielleicht trägt aber ja Dein Case auch dazu bei - wäre schön, zu wissen, was welchen zeitlichen Effekt bringt.
Außerdem braucht es für einen Vergleich auch Hardware-Informationen.
AW: Nicht gleich so angefasst reagieren, onur ...
08.08.2018 19:53:14
onur
Teste doch einfach.
https://www.herber.de/bbs/user/123219.xlsm
Keine Ahnung, was der Kollege für einen Rechner hat, aber bei diesem Unterschied (Habe seinen Code nicht getestet) evtl ein 486er?
AW: Nicht gleich so angefasst reagieren, onur ...
09.08.2018 14:25:02
Leif
Hallo Onur,
vielen Dank für deine Hilfe. Leider braucht die Programmierung auch ähnlich lange wie die originale.
Vielleicht geht es ja auch anders?
Gruß
Leif
AW: Makro beschleunigen
08.08.2018 20:50:52
Daniel
Hi
ein eintscheidender Weg zur Beschleunigung von Makros ist, dass man Veränderungen an mehreren Zellen möglichst nicht für jede Zelle einzeln, sondern immer als Block für den gesamten Zellbereich durchführt.
Excel kann dann die Aktionen, die die Zelländerung bewirkt, für alle Zellen in einem Schritt durchführen und muss sie nicht bei jeder einzelnen Zelle wiederholen.
hierzu sammelt man die auszublendenden Zeilen per Union in einer Variablen und blendet dann über diese Variable alle Zeilen gemeinsam aus.
Sub Schaltfläche1_Klicken()
Application.ScreenUpdating = False
Dim i As Integer
Dim ws As Worksheet
Dim Bereich As Range
For Each ws In ThisWorkbook.Worksheets
Select Case ws.Name
Case "4910xx", "4910100101", "4910107202", "4910100103", "4910100104", "4910100105", _
"4910107206"
With ws
Set Bereich = Nothing
For i = 23 To 311
If .Cells(i, 19).Value  "hat Wert" Then
If Bereich Is Nothing Then
Set Bereich = Rows(i)
Else
Set Bereich = Union(Bereich, Rows(i))
End If
End If
Next i
.Rows.Hidden = False
If Not Bereich Is Nothing Then Bereich.EntireRow.Hidden = True
End With
End Select
Next ws
Application.ScreenUpdating = True
End Sub
Gruß Daniel.
AW: Makro beschleunigen
09.08.2018 14:17:17
Leif
Hallo Daniel,
vielen Dank für deine Antwort. Dein Makro blendet leider alle Zellen in dem Blatt 4910xx zwischen 23-311 aus und prüft nicht ob in der Spalte 19 "hat Wert" steht. Des Weiteren werden die anderen Blätter vom Makro nicht bearbeitet.
Hast du eine Idee warum nicht?
Gruß
Leif
AW: Makro beschleunigen
09.08.2018 14:43:22
Daniel
UPS Fehler
Ergänze mal hier
                        If Bereich Is Nothing Then
Set Bereich = Rows(i)
Else
Set Bereich = Union(Bereich, Rows(i))
End If
Vor den beiden Rows(i) den Punkt: .Rows(i)
Sonst geht dieser bezug immer auf das Aktive Blatt und nicht auf WS.
Gruß Daniel
AW: Makro beschleunigen
09.08.2018 09:28:36
MatthiasG
Hallo Leif,
Das Ein-/ Ausblenden von Zellen erzwingt deren Neuberechnung. Könntest du mal schauen, wie lange die vollständige Berechnung deiner Mappe dauert?
Dazu darf nur die relevante Mappe geöffnet sein (Weil Excel so alle offenen Mappen neu berechnet):

Sub AllesBerechnen()
Dim t1 As Single, t2 As Single
t1 = Timer
Application.CalculateFullRebuild
t2 = Timer
MsgBox "Dauer: " & t2 - t1 & " s"
End Sub

Sollte die Berechnung recht lange dauern, ergibt es evtl. Sinn, nur jene Zeilen ein- bzw. auszublenden, die es noch nicht sind. Dass also der Status vorher geprüft wird.
Gruß Matthias
AW: Makro beschleunigen
09.08.2018 14:18:42
Leif
Hallo Matthias,
die Zeit die angezeigt wurde ist 6,69 Sek!
Gruß
Leif
AW: Makro beschleunigen
09.08.2018 14:23:24
MatthiasG
Hallo Leif,
ok; dann kann es nicht an langen Berechnungszeiten durch Ein-/Ausblenden liegen.
Du hast doch die Berechnung vor dem Makro auf manuell und am Ende wieder zurückgesetzt?
Ich hab etwas die Übersicht verloren in diesem Thread.
Gruß Matthias
AW: Makro beschleunigen
09.08.2018 14:27:39
Leif
Hallo Matthias,
bitte hilf mir kurz, welchen Teil bzw. Variante der Makros soll ich verwenden? Ich bin da nicht so fit drin.
Danke und Gruß
Leif
AW: Makro beschleunigen
09.08.2018 14:37:58
Leif
Hallo Matthias,
das läuft bei dem Punkt:
.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
auf einen Fehler, da das Blatt einen Blattschutz hat.
Gruß
Leif
AW: Makro beschleunigen
09.08.2018 14:44:09
MatthiasG
Hallo Leif,
Blattschutz? Davon erzählst du zum ersten Mal...
Naja, dann musst du den Blattschutz am Anfang des Makros entfernen und am Ende wieder setzen.
ws.Unprotect "Password"
.
.
.
ws.Protect "Password"
Details findest du auch über Google (Excel VBA Blattschutz).
Gruß Matthias
AW: Makro beschleunigen
09.08.2018 14:51:53
Leif
Hallo Matthias,
sorry wusste nicht das dies relevant ist, da es bisher auch so funktioniert hat.
Ich habe jetzt es einmal ohne Blattschutz ausprobiert. Witziger weise werden alle Zeilen nun Eingeblendet! Eigentlich sollten die, wo in Spalte 19 kein "hat Wert" drin steht, ausgeblendet werden.
Danke und Gruß
Leif
AW: Makro beschleunigen
09.08.2018 14:55:59
MatthiasG
Hallo Leif,
Ich sehe nicht, welchen Code du nun verwendest.
ich schlage vor, du beginnst einen neuen Thread mit Verweis auf den alten.
Oder zu fragst gezielt bei den Personen nach, deren Code du jetzt verwendet hast.
Gruß Matthias

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige