Microsoft Excel

Herbers Excel/VBA-Archiv

Macros schneller machen

Betrifft: Macros schneller machen von: Bon
Geschrieben am: 07.08.2014 08:17:36

Hallo zusammen!
Ich habe einen Code um Zeilen in einer großen Excel-Datei zu löschen. Leider dauert es sehr lange, obwohl ich bereits ScreenUpdating = False eingefügt habe.
Habt ihr eine Idee, wie man es schneller machen könnte?

Sub ZeilenLoeschenaktuellesMonat()
Dim Zeile%
Dim x
With Application
  .ScreenUpdating = False
    .Calculation = xlCalculationManual
    .EnableEvents = False
End With

Sheets("aktuelles_Monat").Select
For Zeile = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
    If Cells(Zeile, 10) = "a" Then x = 1 'wenn Zelle leer
    If Cells(Zeile, 10) = "b" Then x = 1 'wenn Zelle =?
    If Cells(Zeile, 10) = "c" Then x = 1
    If Cells(Zeile, 10) = "d" Then x = 1
    If x = 1 Then
        ActiveSheet.Rows(Zeile & ":" & Zeile).Delete
        x = 0
   
    With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
    End With
 End If

Next
End Sub
Sub ZeilenLoeschenVormonat()
Dim Zeile%
Dim x
With Application
  .ScreenUpdating = False
    .Calculation = xlCalculationManual
    .EnableEvents = False
End With

Sheets("Vormonat").Select
For Zeile = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
    If Cells(Zeile, 10) = "a" Then x = 1 'wenn Zelle leer
    If Cells(Zeile, 10) = "b" Then x = 1 'wenn Zelle =?
    If Cells(Zeile, 10) = "c" Then x = 1
    If Cells(Zeile, 10) = "d" Then x = 1
    If x = 1 Then
       ActiveSheet.Rows(Zeile & ":" & Zeile).Delete
       x = 0
       With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
     End With
    End If
Next
End Sub

  

Betrifft: AW: Macros schneller machen von: Robert
Geschrieben am: 07.08.2014 08:23:48

Hallo Bon,

Variablen immer so genau wie möglich angeben!

also:
Dim Zelle as Integer
Dim x as Boolean (und dann entsprechend true und false verwenden)

UND:
Auf das x kann verzichtet werden, wird dann halt etwas unübersichtlicher:

If Cells(Zeile, 10) = "a" OR Cells(Zeile, 10) = "b" OR Cells(Zeile, 10) = "c" OR Cells(Zeile,  _
10) = "d" Then
ActiveSheet.Rows(Zeile & ":" & Zeile).Delete
Viele Grüße
Robert


  

Betrifft: AW: Macros schneller machen von: Luschi
Geschrieben am: 07.08.2014 08:28:11

Hallo Bon,

das Wieder-Einschalten der Excel-Bremsen, geschieht an der falschen Stelle:

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

Sheets("aktuelles_Monat").Select
For Zeile = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
    If Cells(Zeile, 10) = "a" Then x = 1 'wenn Zelle leer
    If Cells(Zeile, 10) = "b" Then x = 1 'wenn Zelle =?
    If Cells(Zeile, 10) = "c" Then x = 1
    If Cells(Zeile, 10) = "d" Then x = 1
    If x = 1 Then
        ActiveSheet.Rows(Zeile & ":" & Zeile).Delete
        x = 0
    End If
Next Zeile

With Application
   .ScreenUpdating = True
   .Calculation = xlCalculationAutomatic
   .EnableEvents = True
End With
Gruß von Luschi
aus klein-Paris


  

Betrifft: AW: Macros schneller machen von: Daniel
Geschrieben am: 07.08.2014 09:11:15

Hi Robert
Wer bei einem Code, der Zeilen einzeln per Schleifen löscht, als erstes Auf die Variablen schaut, beweist leider, dass er von Excel nicht viel versteht. Die Variablentypen haben hier den geringsten Einfluss auf die Laufzeit.
Außerdem ist es so, dass Long und Integer die gleiche Verarbeitungszeit benötigen, so dass die Verwendung von Integer nichts bringt außer Problemen, wenn die Datei mehr als 32000 Zeilen hat.
Die Unterscheidung der Datentypen Byte, Integer, Long war früher auf einem 8-Bit-Prozessor sinnvoll, die heutigen 32-bit-Maschinen können aber den Typ Long in einem Takt verarbeiten.
(ein LKW, der 32 Tonnen laden darf fährt auch nicht schneller, wenn er nur mit 16 Tonnen beladen ist)

Gruß Daniel


  

Betrifft: Und schon wieder der Daniel von: Robert
Geschrieben am: 07.08.2014 10:23:58

Hallo Daniel...

Das zweite Mal, dass du mir in dieser Woche negativ auffällst bzw. persönlich abfällig über Andere urteilst, ohne Hintergründe zu kennen.

Bons Code war gar nicht schlecht, ich habe, weil ich nur flüchtig drübergeschaut habe die Hauptbremse übersehen, nämlich dass die Application. Optionen noch in der For...Next Schleife wieder eingeschaltet werden.

Zum Thema Variablen:
Mag sein, dass zwischen Integer und Long heutzutage durch den Prozessor kein Unterschied mehr besteht.

Dein Beispiel mit dem 32 Tonner ist allerdings vollkommener Schwachsinn, natürlich läuft der schneller, wenn er leichter beladen ist. Du bist mit deinem PKW auch schneller, wenn du statt mit den 5 möglichen Personen alleine drin sitzt.
Um auf deinem Niveau zurückzuschießen:
Das zeigt, wie gut du logische Zusammenhänge in der realen Welt begreifst, nämlich kaum!

ABER:
Es besteht ein Unterschied, ob man eine Variable als Variant deklariert, oder direkt als Long/Integer!
Denn so muss Excel jedesmal doppelte Arbeit machen.

Zu Deinen Beitrag zum Thema:
Klar geht es schnell, mit Hilfe von "Duplikate entfernen" Zeilen zu löschen.
Irgendwie scheint das aber deine Standardantwort zu sein, sobald du irgendwo das Signalwort "Zeilen löschen" liest.
Nicht jeder will eine Hilfsspalte!
Nicht bei jeder Tabelle ist die Duplikate Löschen Funktion hilfreich, da sie nur über begrenzte Einstellmöglichkeiten verfügt, was beispielsweise die Auswahl der zu durchsuchenden Zeilen betrifft.

Just sayin nobody is perfect!
Da Du ein typisches Beispiel dafür bist, was mit der Gesprächskultur der Leute im Internet passiert, werde ich in Zukunft nicht mehr darauf eingehen!

Ich wünsche mir hiermit, dass du auf keinen meiner zukünftigen Beiträge antwortest, und zwar unabhängig davon, ob es ein Kommentar zu mir persönlich oder zu meiner Arbeit ist

Vielen Dank!
Robert


  

Betrifft: AW: Und schon wieder der Robert von: Daniel
Geschrieben am: 07.08.2014 12:53:01

Robert schrieb

Dein Beispiel mit dem 32 Tonner ist allerdings vollkommener Schwachsinn, natürlich läuft der  _
schneller, wenn er leichter beladen ist. Du bist mit deinem PKW auch schneller, wenn du statt mit den 5 möglichen Personen alleine drin sitzt.
Um auf deinem Niveau zurückzuschießen:
Das zeigt, wie gut du logische Zusammenhänge in der realen Welt begreifst, nämlich kaum!
danke für die Aufklärung.
ich dachte immer die Masse würde nur das Beschleunigungvermögen beeinflussen und nicht die Höchstgeschwindigkeit.
Bei der Höchstgeschwindigkeit hatte ich immer gelaubt, diese würde in erste Line bei LKWs per Gesetzt/Technische Einrichtungen auf 80 km/h limitert, und bei unlimitierten Fahrzeugen durch den Luftwiderstand.
Aber nochmal Danke, das du mir zumindest hier hilfst, die Zusammenhänge in der realen Welt besser zu verstehen.

mal zu den ernsteren Themen:
ich verwende das Duplikate entfernen standardmäßig zum Löschen von Zeilen, weil es
- sehr schnell ist
- den geringsten Programmieraufwand verursacht und absolut einfach zu programmieren ist (keine Variablen, keine Schleifen, keine Bedingungsprüfungen, keine exotischen VBA-Funktionen)
- keine Eingriffe in die Datenstrukur erfordert um schnell zu sein (wie z.B. Sortieren)
- auch bei grössten Datenmengen funktioniert (auch bei 1,04 Mio Zeilen)
- auch ohne VBA von Hand anwendbar ist
- durch die Formel alle Zeilen zuerst gekennzeichnet werden, bevor sie gelöscht werden. Dadurch habe ich die möglichkeit, meinen Code (dh in diesem Fall die Formel) auf richtigkeit zu prüfen bevor die Daten verändert werden, dh ich kann Korrekturen vornehmen, und den Code weiter laufen lassen, ohne dass ich erst den ursprünglichen Datenstand wiederherstellen muss (was aufwendig sein kann, da es für durch Änderungen durch Makro kein Undo gibt)

Alle anderen Löschmethoden haben diese Vorteile nicht oder nur teilweise, weswegen meiner Ansicht nach für die aktuellen Excelversionen (2007-2013) das Duplikate-Entfernen einfach die beste Löschmethode von Zeilen in grossen Tabellen ist
Warum sollte ich jemand anderem dann was anderes empfehlen als das, was ich für das beste halte?


zum Thema Hilfszeilen:
ja ich weiss, viele wollen keine Hilfszeilen.
Es gibt aber so gut wie nie einen realen Grund, der die Verwendung von Hilfsspalten verbietet, zumal diese ja nach Verwendung sofort wieder gelöscht werden und somit keinen Einfluss auf die eigentliche Tabelle haben.

Thema mangelnde Variabilität:
die Einstellmöglichkeiten von Duplikate entfernen sind doch für diese Aufgabe unwichtig.
die Entscheidung, ob eine Zeile gelöscht werden muss oder nicht wird ja durch die Formel erledigt und dadurch hast du sehr viele Möglichkeiten um die Löschbedingungen zu formulieren.

Gruß Daniel


  

Betrifft: AW: Macros schneller machen von: Hajo_Zi
Geschrieben am: 07.08.2014 08:27:56

lösche nicht einzeln sondern mit einmal.

Sub Leerzeilen_loeschen()
    '   alle Leerzeilen löschen
    Dim LoI As Long
    Dim RaZeile As Range
    Application.ScreenUpdating = False
    For LoI = 1 To ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
        If Application.WorksheetFunction.CountA(Rows(LoI)) <> ActiveSheet.UsedRange. _
SpecialCells(xlCellTypeLastCell).Column Then
            If Rows(LoI).SpecialCells(xlCellTypeBlanks).Count = ActiveSheet.UsedRange. _
SpecialCells(xlCellTypeLastCell).Column Then
                If RaZeile Is Nothing Then
                    Set RaZeile = Rows(LoI)
                Else
                    Set RaZeile = Union(RaZeile, Rows(LoI))
                End If
            End If
        End If
    Next LoI
    If Not RaZeile Is Nothing Then RaZeile.Delete
    Application.ScreenUpdating = True
    Set RaZeile = Nothing
End Sub
GrußformelHomepage


  

Betrifft: AW: Macros schneller machen von: Daniel
Geschrieben am: 07.08.2014 09:35:24

Hi
schnelles Löschen von Zeilen geht so

1. Schreibe in eine Hilfsspalte eine Formel, welche alle Zeilen die gelöscht werden sollen mit 0 markiert und alle die stehenbleiben müssen mit der aktuellen Zeilennummer.
Für deinen Fall wäre die Formel:

=wenn(istfehler(finden(j1;"abcd"));Zeile();0)
2. Schreibe in die erste Zeile der Hilfsspalten ebenfalls die 0

3. Wende auf die ganze Tabelle die Funktion Daten - Datentools - Duplikate entfernen an.
Verwende die Hilfsspalten als Kriterium und die Einstellung "keine Überschrift"

4. Leere die Hilfsspalten

Kannst du diese 4 schritte ohne Hilfe als Makro schreiben?
(der Recorder ist dein Freund)

Gruß Daniel


  

Betrifft: AW: Macros schneller machen von: Bon
Geschrieben am: 07.08.2014 11:13:01

Hallo zusammen,

vielen, vielen Dank für eure Antworten!
Ich bin für alle Antworten und Vorschläge sehr dankbar, da ich selbst ein absoluter Laie bin.

@ Luschi und Robert: Vielen Dank für euere Anpassungen. Ich hab sie verstanden und habe hoffentlich auch für zukünftige Problemstellungen dazu gelernt ;).
@ Daniel: Die Hilfsspalte und die Suche nach Duplikaten beschleunigt das Ganze natürlich sehr.
@Hajo_Zi: Auch vielen Dank für deinen Beitrag, auch wenn ich als Excel Laie den Code nicht anpassen konnte.

Danke! Bon


 

Beiträge aus den Excel-Beispielen zum Thema "Macros schneller machen"