Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
916to920
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
916to920
916to920
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Inhaltsverzeichnis

Makro wird nach Autofilter langsamer

Makro wird nach Autofilter langsamer
15.10.2007 14:01:00
NoNet
Hallo Leute,
heute beschäftigt mich folgendes Problem :
In einem Tabellenblatt befindet sich eine recht lange Daten-Liste (20 Spalten, ca. 12.000 Zeilen) und 3 CommandButtons :
- -
Hinter diesen 3 Commandbuttons verbergen sich je ein Makro :
- "Filtern nach Datum" : Zeigt ein UserForm an, in dem ein START- und ein ENDE-Datum eingegeben wird und die Liste in Spalte Q (=Spalte 17) nach diesem Datum gefiltert wird (per Autofilter).
- "Autofilter aus" : Deaktiviert die Autofilter und zeigt wieder die komplette Liste an
- "Datenmaske" : Zeigt ein UserForm an, in dem die Daten editiert werden können (ähnlich des Befehls "Daten - Maske", jedoch mit ein paar Besonderheiten, daher selbst programmiert).
In diesem UserForm befindet sich u.a. ein Commandbutton "Letzten Datensatz kopieren", mit dem der letzte Datensatz der (sortierten) Liste im UF angezeigt wird, und nach Bestätigung als neuer Datensatz in der Liste hinzugefügt wird.
Jetzt das Problem : Wenn die Mappe geöffnet wurde, funktioniert das Kopieren des letzten Datensatzes sehr schnell (gemessen : deutlich unter 1 Sek.). Diesen vorgang kann man beliebig oft wiederholen, ohne spürbaren Geschwindigkeitsunterschied.
Wenn nun aber die Liste einmalig über den Autofilter gefiltert wurde (egal ob manuell oder über das MAkro !) und dieser wieder deaktiviert wurde, dauert das Kopieren der Datensätze nun deutlich länger : ca. 1-2 Sekunden !
Wie kann das passieren ? Bzw. : Wie kann man das verhindern ? Gibt es eine Art "Cache" des Autofilters, der die Geschwindigkeit reduziert (Ähnlich PivotCache oder TableQueries) ? Wenn ja: Wie kann man diesen per VBA löschen ?
Ich habe bereits die Alternative per Spezialfilter (bzw. VBA: "AdvancedFilter") probiert, damit dauert es noch länger. Auch das Speichern der Mappe und das damit verbundene Löschen der "Rückgängig-Schritte" brachte keinen Erfolg ! ebensowenig half das Deaktivieren des Sortiervorgangs im Makro :-(
Hat jemand noch eine andere Idee, wie man NACH dem Filtern die gleiche Geschwindigkeit erreichen kann wie VOR dem Filtern ?


Hier ein paar Code-Auszüge :
Mit diesem Makro (im UF "Filtern") wird die Liste nach Datum gefiltert :
Private Sub CommandButton1_Click()
    'Das Makro zum Filtern der Liste nach Datum_VON und Datum_BIS :
    Dim lz
    Dim shBew As Worksheet
    Set shBewetterung = Sheets("Bew")
    shBewetterung.AutoFilterMode = False
    lz = shBewetterung.Cells(Rows.Count, "A").End(xlUp).Row
    shBewetterung.Range("A3:R" & CStr(lz)).AutoFilter Field:=17, _
        Criteria1:=">=" & CDbl(CDate(TextBox1)), Operator:=xlAnd, _
        Criteria2:="<=" & CDbl(CDate(TextBox2))
    '
    Range("A4").CurrentRegion.Sort Key1:=Range("J4"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    UserForm2.Hide
    ActiveWindow.View = xlPageBreakPreview
End Sub


Mit diesem Makro wird der Autofilter (bzw. auch Spezialfilter) deaktiviert :
Public Sub filter_aus()
    If ActiveSheet.AutoFilterMode Then
        ActiveSheet.AutoFilterMode = False
    ElseIf ActiveSheet.FilterMode Then
        ActiveSheet.ShowAllData
    End If
    If Err.Number = 1004 Then Exit Sub
    letztezeile = Range("A4").End(xlDown).Row
    Range("A4:P" & letztezeile).Sort Key1:=Range("E4"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    ActiveWindow.View = xlNormalView
    [A4].End(xlDown).Select 'Letzte Zeile markieren
End Sub


Mit diesem Makroteil wird der letzte Datensaz in das UF "Datenmaske" eingelesen :
Private Sub cmbkopieren_Click()
    'Daten aus der Liste in die Datenmaske kopieren :
    Application.ScreenUpdating = False
    letztezeile = Range("A4").End(xlDown).Row
    UserForm1.cmbname = Range("A" & letztezeile).Value
    UserForm1.Txtauftragsnummer = Range("B" & letztezeile).Value
    UserForm1.Txtabteilung = Range("C" & letztezeile).Value
    'etc. : Noch weitere Felder im gleichen Stil
    Application.ScreenUpdating = True
End Sub


Mit diesem Makroteil wird der geänderte Datensatz als neue Datenzeile wieder in die Liste zurückgeschrieben.
Hier tritt das Problem auf, dass das nach dem Filtern recht lange dauert :


Private Sub Einfuegen()
    Dim letztezeile
    'Status des Berechnungsmodus speichern und auf "MANUELL" setzen :
    Dim calcSave
    calcSave = Application.Calculation
    Application.Calculation = xlCalculationManual 'Berechnung auf MANUELL !
    Sheets("Bewetterungen I99").Select
    'Erst mal gesamte Zeile kopieren :
    Range("A" & letztezeile & ":R" & letztezeile).Copy Range("A" & letztezeile + 1)
    'Änderungen aus dem UF in die Zeile kopieren :
    On Error Resume Next
    Range("A" & letztezeile + 1).Value = UserForm1.cmbname
    Range("B" & letztezeile + 1).Value = UserForm1.Txtauftragsnummer
    Range("C" & letztezeile + 1).Value = UserForm1.Txtabteilung
    'etc. : weitere Felder im gleichen Stil in die Liste zurückschreiben....
    Unload UserForm1 'die "Datenmaske" entladen
    Range("A4").End(xlDown).Select
    Application.Calculation = calcSave 'Berechnung wieder zurückstellen
End Sub


Vielen Dank für eure Unterstützung,
Gruß NoNet

3
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Makro wird nach Autofilter langsamer
15.10.2007 14:48:01
Heiko
Hallo NoNet,
habe das jetzt nicht alles kontrolliert, aber im Private Sub Einfuegen()
wir mit Dim letztezeile die Variable LetzteZeile dimensioniert.
In den Subs vorher wird der Variablen LetzteZeile ein Wert zugeordnet ohne das Sie dort irgendwo dimensioniert wird.
Hat du Sie schon als Public außerhalb der Subs dimensioniert, oder arbeitest du ohne Option Explicit das sich dein VBA seine Varis selbst deklarieren darf.
Was ich sagen will, manchmal führen solche mehrfach Deklarierungen zu Problemen, ob das dann auch zu einem langsameren Ablauf führt, must du mal prüfen indem du die Variable LetzteZeile Einmal Sauber deklarierst.
Gruß Heiko
PS: Rückmeldung wäre nett !

Anzeige
Gute Idee, aber das war's leider nicht
15.10.2007 15:03:00
NoNet
Hallo Heiko,
herzlichen Dank erstmal für Deine Idee - da hast Du gut mitgedacht :
Das Proggi stammt nicht von mir, ich "darf" es nur korrigieren ;-)
Es war in der Tat in jedem Modul und in den UFs bereits ein "Option Explicit" und ein "Dim LetzteZeile" auf Modulebene enthalten.
Das Makro "Einfuegen" hatte ich bereits modifiziert, daher habe ich in diesem SUB nochmal eine eigene Variable deklariert - mein Fehler. Aber selbst ohne diese redundante (bzw. : lokale) Deklararation wird es nicht schneller :-(
Die Suche geht also weter und die Frage bleibt offen...
Gruß, NoNet

Anzeige
AW: Makro wird nach Autofilter langsamer
15.10.2007 15:02:00
Renee
Hi NoNet,
Ich sehe in Deinem Makro Einfuegen nirgends, wie oder wo du den Inhalt der Variablen letztezeile bestimmst ?
GreetZ Renee

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige