Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

Makro beschleunigen


Betrifft: Makro beschleunigen von: Leif
Geschrieben am: 08.08.2018 15:12:58

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

  

Betrifft: AW: Makro beschleunigen von: Mister-B
Geschrieben am: 08.08.2018 15:22:01

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


  

Betrifft: Zwei Alternativen von: lupo1
Geschrieben am: 08.08.2018 15:27:44

a) den Hidden-Range mittels Verketten vorbesetzen und dann über Union zuweisen
b) Autofilter


  

Betrifft: AW: Makro beschleunigen von: ChrisL
Geschrieben am: 08.08.2018 15:55:49

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


  

Betrifft: AW: Makro beschleunigen von: ChrisL
Geschrieben am: 08.08.2018 16:12:50

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


  

Betrifft: AW: Makro beschleunigen von: MatthiasG
Geschrieben am: 08.08.2018 17:34:21

Hallo Zusammen,

seit Excel 2003 berechnet Excel auch Zellen neu, die ein- oder ausgeblendet werden.
Das hab ich damals schmerzhaft erfahren müssen:
https://www.herber.de/forum/archiv/772to776/772677_Zeile_einblenden_erzwingt_Neuberechnung.html

Ich habe nicht geprüft, ob sich etwas verändert hat seitdem, aber das würde die lange Rechenzeit erklären.

Gruß Matthias


  

Betrifft: Mir wäre neu, dass JEMALS nicht alles bei Auto- von: lupo1
Geschrieben am: 08.08.2018 17:47:05

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.


  

Betrifft: AW: Mir wäre neu, dass JEMALS nicht alles bei Auto- von: MatthiasG
Geschrieben am: 09.08.2018 14:53:47

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


  

Betrifft: AW: Makro beschleunigen von: Leif
Geschrieben am: 09.08.2018 15:03:28

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


  

Betrifft: AW: Makro beschleunigen von: ChrisL
Geschrieben am: 09.08.2018 15:27:09

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



  

Betrifft: AW: Makro beschleunigen von: Leif
Geschrieben am: 09.08.2018 15:45:49

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


  

Betrifft: und wie lange dauert Variante 2? owT. von: ChrisL
Geschrieben am: 09.08.2018 15:46:44

.


  

Betrifft: AW: und wie lange dauert Variante 2? owT. von: Leif
Geschrieben am: 10.08.2018 14:56:04

Hallo Chris,

Variante 2 dauert ca. 45-60 Sekunden.

Gruß
Leif


  

Betrifft: AW: und wie lange dauert Variante 2? owT. von: MatthiasG
Geschrieben am: 10.08.2018 15:04:27

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


  

Betrifft: Neustart - Ausschluss Neuberechnung als Ursache von: ChrisL
Geschrieben am: 10.08.2018 15:59:53

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


  

Betrifft: AW: Makro beschleunigen von: Leif
Geschrieben am: 08.08.2018 16:57:49

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


  

Betrifft: AW: Makro beschleunigen von: ChrisL
Geschrieben am: 08.08.2018 17:45:37

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



  

Betrifft: AW: Makro beschleunigen von: Leif
Geschrieben am: 09.08.2018 15:22:56

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


  

Betrifft: AW: Makro beschleunigen von: onur
Geschrieben am: 08.08.2018 18:01:26

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



  

Betrifft: Der Bottleneck ist nicht das Verkürzen der boole- von: lupo1
Geschrieben am: 08.08.2018 18:27:41

schen Bedingung, sondern das einzelne .Hidden-Zuweisen.

Zeitlich verglichen hast Du die TE-Lösung und Deine sicherlich nicht?


  

Betrifft: AW: Der Bottleneck ist nicht das Verkürzen der boole- von: onur
Geschrieben am: 08.08.2018 19:20:26

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


  

Betrifft: Nicht gleich so angefasst reagieren, onur ... von: lupo1
Geschrieben am: 08.08.2018 19:32:42

... 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.


  

Betrifft: AW: Nicht gleich so angefasst reagieren, onur ... von: onur
Geschrieben am: 08.08.2018 19:53:14

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?


  

Betrifft: AW: Nicht gleich so angefasst reagieren, onur ... von: Leif
Geschrieben am: 09.08.2018 14:25:02

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


  

Betrifft: AW: Makro beschleunigen von: Daniel
Geschrieben am: 08.08.2018 20:50:52

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.


  

Betrifft: AW: Makro beschleunigen von: Leif
Geschrieben am: 09.08.2018 14:17:17

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


  

Betrifft: AW: Makro beschleunigen von: Daniel
Geschrieben am: 09.08.2018 14:43:22

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


  

Betrifft: AW: Makro beschleunigen von: MatthiasG
Geschrieben am: 09.08.2018 09:28:36

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


  

Betrifft: AW: Makro beschleunigen von: Leif
Geschrieben am: 09.08.2018 14:18:42

Hallo Matthias,

die Zeit die angezeigt wurde ist 6,69 Sek!

Gruß
Leif


  

Betrifft: AW: Makro beschleunigen von: MatthiasG
Geschrieben am: 09.08.2018 14:23:24

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


  

Betrifft: AW: Makro beschleunigen von: Leif
Geschrieben am: 09.08.2018 14:27:39

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


  

Betrifft: AW: Makro beschleunigen von: MatthiasG
Geschrieben am: 09.08.2018 14:35:01

hast du das von ChrisL probiert?
https://www.herber.de/forum/messages/1638219.html

Gruß Matthias


  

Betrifft: AW: Makro beschleunigen von: Leif
Geschrieben am: 09.08.2018 14:37:58

Hallo Matthias,

das läuft bei dem Punkt:
.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True

auf einen Fehler, da das Blatt einen Blattschutz hat.

Gruß
Leif


  

Betrifft: AW: Makro beschleunigen von: MatthiasG
Geschrieben am: 09.08.2018 14:44:09

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


  

Betrifft: AW: Makro beschleunigen von: Leif
Geschrieben am: 09.08.2018 14:51:53

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


  

Betrifft: AW: Makro beschleunigen von: MatthiasG
Geschrieben am: 09.08.2018 14:55:59

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