Excel - Leere (Zwischen-) Zeilen löschen

1. offizieller Beitrag

    Hallo liebe Coder,

    ich habe ein kleines Problem beim Löschen der (Zwischen-) Zeilen u. wär euch dankbar wenn Ihr mir hier behilflich sein könntet.

    Ich habe eine große Excel-Datei mit den Spalten A-J und ca. 100.000 Zeilen.
    Nun möchte ich über Autoit alle leeren Zeilen löschen, wobei eine Zeile nur dann leer ist, wenn sich in KEINER der Spalten ein Eintrag befindet, also A:x bis J:x leer sind.

    Mein Code funktioniert zwar, allerdings benötigt er für 800 Zeilen ca. 1 Minute, was bei 100.000 Zeilen über 2 Stunden wären.
    Die letzte Zeile erkenne ich momentan daran (siehe Code), dass nach dem letzten erkannten Inhalt 5 Leerzeilen folgen (da auch mal 3 Leerzeilen nacheinander vorkommen). Wie könnte man das eleganter lösen? Mein Code basiert auf der Excel UDF bin aber gerade eben darauf aufmerksam geworden, dass man es auch über die Excel Objekte (COM) lösen könnte, nur leider hab ich darin keine Erfahrungen. Weiß jemand wie man das realisieren könnte? Wenn möglich würd ich es gern ohne VBA lösen.

    Hier mein (umständlicher) Code. Beim Ausführen müsst ihr nur die $Excel_Pfad_Zieldatei anlegen :

    Danke vorab

    [autoit][/autoit][autoit][/autoit][autoit]

    #include <Excel.au3>

    [/autoit][autoit][/autoit][autoit]

    HotKeySet("{ESC}", "Terminate")
    Func Terminate()
    Exit 0
    EndFunc

    [/autoit][autoit][/autoit][autoit]

    Global $Spalte_A = "A"
    Global $Spalte_B = "B"
    Global $Spalte_C = "C"
    Global $Spalte_D = "D"
    Global $Spalte_E = "E"
    Global $Spalte_F = "F"
    Global $Spalte_G = "G"
    Global $Spalte_H = "H"
    Global $Spalte_I = "I"
    Global $Spalte_J = "J"
    Global $Startzeile = 2

    [/autoit][autoit][/autoit][autoit]

    Global $Excel_Pfad_Zieldatei = @ScriptDir & "\Extras\_Excel1.xlsx"
    Global $Sleep_nach_While = 100

    [/autoit][autoit][/autoit][autoit]

    ; Excel-Zieldatei öffnen
    $oExcel = _Excel_Open()
    $oZieldatei = _Excel_BookOpen($oExcel, $Excel_Pfad_Zieldatei)

    [/autoit][autoit][/autoit][autoit]

    ; Leerzeilen löschen
    While 1
    Sleep($Sleep_nach_While)
    ToolTip("Leere Zeilen werden gelöscht. Aktueller Fortschritt: Zeile "&$Startzeile)
    Local $Inhalt_A=_Excel_RangeRead($oZieldatei, Default, $Spalte_A&$Startzeile)
    If $Inhalt_A="" Then
    Local $Inhalt_B=_Excel_RangeRead($oZieldatei, Default, $Spalte_B&$Startzeile)
    If $Inhalt_B="" Then
    Local $Inhalt_C=_Excel_RangeRead($oZieldatei, Default, $Spalte_C&$Startzeile)
    If $Inhalt_C="" Then
    Local $Inhalt_D=_Excel_RangeRead($oZieldatei, Default, $Spalte_D&$Startzeile)
    If $Inhalt_D="" Then
    Local $Inhalt_E=_Excel_RangeRead($oZieldatei, Default, $Spalte_E&$Startzeile)
    If $Inhalt_E="" Then
    Local $Inhalt_F=_Excel_RangeRead($oZieldatei, Default, $Spalte_F&$Startzeile)
    If $Inhalt_F="" Then
    Local $Inhalt_G=_Excel_RangeRead($oZieldatei, Default, $Spalte_G&$Startzeile)
    If $Inhalt_G="" Then
    Local $Inhalt_H=_Excel_RangeRead($oZieldatei, Default, $Spalte_H&$Startzeile)
    If $Inhalt_H="" Then
    Local $Inhalt_I=_Excel_RangeRead($oZieldatei, Default, $Spalte_I&$Startzeile)
    If $Inhalt_I="" Then
    Local $Inhalt_J=_Excel_RangeRead($oZieldatei, Default, $Spalte_J&$Startzeile)
    If $Inhalt_J="" Then

    [/autoit][autoit][/autoit][autoit]

    For $i = 1 To 5 Step 1

    [/autoit][autoit][/autoit][autoit]

    If $i = 5 Then ExitLoop 2

    [/autoit][autoit][/autoit][autoit]

    Local $Inhalt_A_darunter = _Excel_RangeRead($oZieldatei, Default, $Spalte_A&$Startzeile + $i)
    If $Inhalt_A_darunter<>"" Then ExitLoop
    Local $Inhalt_B_darunter = _Excel_RangeRead($oZieldatei, Default, $Spalte_B&$Startzeile + $i)
    If $Inhalt_B_darunter<>"" Then ExitLoop
    Local $Inhalt_C_darunter = _Excel_RangeRead($oZieldatei, Default, $Spalte_C&$Startzeile + $i)
    If $Inhalt_C_darunter<>"" Then ExitLoop
    Local $Inhalt_D_darunter = _Excel_RangeRead($oZieldatei, Default, $Spalte_D&$Startzeile + $i)
    If $Inhalt_D_darunter<>"" Then ExitLoop
    Local $Inhalt_E_darunter = _Excel_RangeRead($oZieldatei, Default, $Spalte_E&$Startzeile + $i)
    If $Inhalt_E_darunter<>"" Then ExitLoop
    Local $Inhalt_F_darunter = _Excel_RangeRead($oZieldatei, Default, $Spalte_F&$Startzeile + $i)
    If $Inhalt_F_darunter<>"" Then ExitLoop
    Local $Inhalt_G_darunter = _Excel_RangeRead($oZieldatei, Default, $Spalte_G&$Startzeile + $i)
    If $Inhalt_G_darunter<>"" Then ExitLoop
    Local $Inhalt_H_darunter = _Excel_RangeRead($oZieldatei, Default, $Spalte_H&$Startzeile + $i)
    If $Inhalt_H_darunter<>"" Then ExitLoop
    Local $Inhalt_I_darunter = _Excel_RangeRead($oZieldatei, Default, $Spalte_I&$Startzeile + $i)
    If $Inhalt_I_darunter<>"" Then ExitLoop
    Local $Inhalt_J_darunter = _Excel_RangeRead($oZieldatei, Default, $Spalte_J&$Startzeile + $i)
    If $Inhalt_J_darunter<>"" Then ExitLoop
    Next
    _Excel_RangeDelete($oZieldatei.Worksheets(1), $Startzeile&":"&$Startzeile)
    If @error Then MsgBox(0, "Fehler", "Fehler beim Löschen!")
    ContinueLoop

    [/autoit][autoit][/autoit][autoit]

    EndIf
    EndIf
    EndIf
    EndIf
    EndIf
    EndIf
    EndIf
    EndIf
    EndIf
    EndIf

    [/autoit][autoit][/autoit][autoit]

    $Startzeile = $Startzeile + 1

    [/autoit][autoit][/autoit][autoit]

    WEnd

    [/autoit][autoit][/autoit][autoit][/autoit][autoit][/autoit]

    2 Mal editiert, zuletzt von tobius (13. Oktober 2014 um 17:26)

    • Offizieller Beitrag

    Das Prüfen auf leer solltest du keinesfalls Zelle für Zelle machen, viel zu langsam.

    Hier mal ein Bsp. wie man einen Bereich (Range) auf leer prüft:

    [autoit]


    $xls = "C:\Temp\Test.xls"
    Local $oExcel = ObjCreate("Excel.Application") ; Excel-Objekt erstellen
    $oExcel.Visible = 1 ; 0=unsichtbar/ 1=sichtbar
    Local $oBook = $oExcel.Workbooks.Open($xls) ; Datei öffnen
    Local $oSheet = $oBook.Sheets(1) ; erstes Tabellenblatt verwenden - bei Bedarf ändern, kann auch Name sein

    [/autoit][autoit][/autoit][autoit]

    $zeile = 26
    If $oExcel.WorksheetFunction.CountA($oSheet.Range("A" & $zeile & ":G" & $zeile)) = 0 Then
    ConsoleWrite('Zeile ' & $zeile & ' ist leer.' & @CRLF)
    Else
    ConsoleWrite('Zeile ' & $zeile & ' ist nicht leer.' & @CRLF)
    EndIf

    [/autoit]

    Wenn du die Zeilen löschen willst: Durchlaufe das Tabellenblatt in einer Schleife von hinten beginnend! Sonst stimmt nach dem Löschen die verwendete Zeilennummer nicht mehr. ;)

    Edit:
    Die einfachste Variante wäre also:

    Spoiler anzeigen
    [autoit]

    $xls = "C:\Temp\Test.xls"
    Local $oExcel = ObjCreate("Excel.Application") ; Excel-Objekt erstellen
    $oExcel.Visible = 1 ; 0=unsichtbar/ 1=sichtbar
    Local $oBook = $oExcel.Workbooks.Open($xls) ; Datei öffnen
    Local $oSheet = $oBook.Sheets(1) ; erstes Tabellenblatt verwenden - bei Bedarf ändern, kann auch Name sein

    [/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

    For $zeile = $oSheet.Cells($oExcel.Rows.Count, 1).End(-4162).Row To 1 Step -1 ; -4162 = xlUp
    If $oExcel.WorksheetFunction.CountA($oSheet.Range("A" & $zeile & ":J" & $zeile)) = 0 Then
    $oSheet.Rows($zeile & ":" & $zeile).Delete
    EndIf
    Next

    [/autoit]

    Jedoch wird hier nur das "normale" Ende des Inhalts berücksichtigt. Dazu muss die erste Spalte einen Inhalt haben um erkannt zu werden. Sollten deine Zeilen nicht immer auch in der ersten Spalte inhalte haben, würden Zeilen ohne Inhalte in A, die am Ende der Tabelle sind, ignoriert werden.

    Hallo BugFix,

    vielen Dank für den Hinweis und deinen Code, funktioniert prima :)
    Sobald er von hinten beginnend in Spalte "A" einen Inhalt erkennt, löscht er alle darüberliegenden Leerzeilen.
    Nun kommt es in einigen meiner Excel-Dateien aber vor, dass in Spalte A erst später etwas steht obwohl in einer der anderen Spalten (B-J) schon früher Inhalt vorhanden ist (von hinten beginnend).
    Wie kann ich ihm sagen, dass er ab dem Zeitpunkt alle darüberliegenden Leerzeilen löschen soll, ab dem er von hinten beginnend das erste mal in einer der Spalten A-J Inhalt findet. Also sozusagen wie dein Code, nur dass die Spalte A mit den anderen Spalten "oder" verknüpft wird?
    Und da die Excel schon vorher offen ist suche ich schon die ganze Zeit, wie ich die Excel-Datei anspreche, anstatt zu öffnen mit

    [autoit]

    $oExcel.Workbooks.Open($xls)

    [/autoit]


    Da fehlt mir noch der rote Faden, daher möcht ich mich nun auch näher mit den Excel Objekten beschäftigten.
    Bitte um Infos, Links etc. wie ich am besten die Syntax dazu lernen kann, wie bspw.:

    [autoit]

    $oExcel.WorksheetFunction.CountA($oSheet.Range("A" & $zeile & ":G" & $zeile))

    [/autoit]

    Gibt es da ein empfehlenswertes Tutorial?
    In Autoit ist das ja recht einfach, da schaut man in die Funktionsreferenz bzw. UDF-Referenz und auf der rechten Seite ist schön beschrieben, was die Funktion macht. Schaut man sich die Funktion dann an, sind alle Parameter schön erklärt u. notfalls findet man unten noch ein oder mehrere Beispiele zum selber testen. Aber wie mach ich das bei diesen Excel Objekten?
    Bspw. möchte ich als nächstes alle Duplikate in meinen Excel-Dateien löschen, jetzt steh ich erstmal vor der folgenden Objekt-Referenzliste und hab keine Ahnung in welcher ich schauen muss:
    http://msdn.microsoft.com/en-us/library/ff846392.aspx

    Könnt ihr mir bitte Hinweise geben wie man da am Besten vorgeht? Oder gibt es eine Datenbank oder ähnliches in der man da schauen kann?

    Danke schon mal

    Grüße
    tobius

    2 Mal editiert, zuletzt von tobius (9. Oktober 2014 um 02:04)

    • Offizieller Beitrag
    Zitat von tobius

    Wie kann ich ihm sagen, dass er ab dem Zeitpunkt alle darüberliegenden Leerzeilen löschen soll, ab dem er von hinten beginnend das erste mal in einer der Spalten A-J Inhalt findet. Also sozusagen wie dein Code, nur dass die Spalte A mit den anderen Spalten "oder" verknüpft wird?

    Dazu brauchst du eine Funktion, die für jede Spalte deines Bereiches einzeln abprüft, welches die letzte benutzte Zeile ist und den höchsten Wert verwendest du dann. Ich habe das mal in meinem Bsp. erstellt.

    Zitat von tobius

    Und da die Excel schon vorher offen ist suche ich schon die ganze Zeit, wie ich die Excel-Datei anspreche, anstatt zu öffnen

    [autoit]

    ObjGet

    [/autoit]

    ;)

    Spoiler anzeigen
    [autoit]


    $xls = "C:\Temp\Test.xls"

    [/autoit] [autoit][/autoit] [autoit]

    ; Wenn Datei geschlossen:
    ;~ Local $oExcel = ObjCreate("Excel.Application") ; Excel-Objekt erstellen
    ;~ $oExcel.Visible = 1 ; 0=unsichtbar/ 1=sichtbar
    ;~ Local $oBook = $oExcel.Workbooks.Open($xls) ; Datei öffnen

    [/autoit] [autoit][/autoit] [autoit]

    ; Wenn Datei bereits offen:
    Local $oExcel = ObjGet('', "Excel.Application") ; wir brauchen eine Referenz auf das Excelobjekt..
    Local $oBook = ObjGet($xls) ; und eine auf das geöffnete Workbook

    [/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

    Local $oSheet = $oBook.Sheets(1) ; erstes Tabellenblatt verwenden - bei Bedarf ändern, kann auch Name sein

    [/autoit] [autoit][/autoit] [autoit]

    For $zeile = _GetLastUsedRow($oSheet, 'A:G') To 1 Step -1
    If $oExcel.WorksheetFunction.CountA($oSheet.Range("A" & $zeile & ":G" & $zeile)) = 0 Then
    $oSheet.Rows($zeile & ":" & $zeile).Delete
    EndIf
    Next

    [/autoit] [autoit][/autoit] [autoit]

    Func _GetLastUsedRow(ByRef $oSheet, $sRange) ; in der Variante aber nur bis Spalte Z !!
    Local $aRange = StringSplit($sRange, ':')
    Local $iStart = Asc(StringUpper($aRange[1])) -64
    Local $iEnd = Asc(StringUpper($aRange[$aRange[0]])) -64
    Local $iLastRow = 0, $iTmp
    For $i = $iStart To $iEnd
    $iTmp = $oSheet.Cells($oSheet.Rows.Count, $i).End(-4162).Row ; -4162 = xlUp
    If $iTmp > $iLastRow Then $iLastRow = $iTmp
    Next
    Return $iLastRow
    EndFunc

    [/autoit]


    Um die entsprechenden Methoden und Properties zu finden ist meine erste Anlaufstelle Excel Object Model Reference und auch VBA Language Reference.
    Aber häufig sind die Probleme schon aufgetaucht und wurden in einem VBA-Forum gepostet. Somit ist Google-Suche mit "VBA SUCHWORT" allererste Wahl. ;)
    Der Großteil an Funktion des täglichen Gebrauchs mit Excel wurde aber in der neuen Excel-UDF von water integriert - also auch immer mal da nen Blick reinwerfen, wenn man mit den VBA-Funktionen nicht so vertraut ist.

    Achja: Für dein Thema Duplikate löschen - Google mal "vba excel duplikate löschen", den Rest schaffst du dann bestimmt selbst. ;)
    Einen Hinweis noch, wenn du dich mit VBA-Funktionen beschäftigst um diese nach AutoIt zu portieren:
    In VBA werden beim Funktionsaufruf generell nur Parameter übergeben, die nicht mit den Standardwerten belegt sind. Das kennen wir auch von AutoIt-Funktionen. Jedoch muss die AutoIt-Variante der VBA-Funktion immer alle Funktionsparameter übergeben bekommen. D.h., wenn du in einem VBA-Code eine Funktion findest musst du zuerst in der Doku nach allen Parametern und den vorbelegten Werten suchen um diese in AutoIt verwenden zu können.

    Hallo Bugfix,

    vielen Dank für dein Beispiel und deine Erläuterungen, das hilft mir doch gleich enorm weiter.
    Super, dass man hier nach VBA-Codes schauen kann, ich hab bisher immer nach "Autoit" gesucht aber da findet man nicht so viel.
    Was meine Meinung nach auch noch hilft, wenn man mal nicht weiß welche Funktion man nehmen soll einfach die gewünschte Aktion mit dem Makro-Recorder in Excel aufzeichnen und anschließend die Funktion in der Objekt-Referenz nachschlagen.

    Danke für deine Hilfe ;)

    Beste Grüße
    tobius