Variable für Zelle nach unten schieben

  • geschlossen
  • Excel

  • FeliX_22
  • 3112 Aufrufe 8 Antworten

  • Variable für Zelle nach unten schieben

    Hallo!

    Ich habe gerade ein kleines Excel-Problem mit VBA. Ich möchte mit VBA folgendes realisieren: (Ich drücks am besten im Pseudo-Sprache aus)

    Set Variable = Zelle A1
    Do
    Variable = Variable *Und Eine Zelle Runter*
    Wenn Variable.Inhalt = Wert Dann GanzeZeilelöschen
    Solange Zelle nicht leer ist

    Ich hoffe das ist verständlich. Es ist folgendes: Ich kann ein Makro ausführen, dass dann den VBA-Code erzeugt. Der Code benutzt aber nur Anweisungen wie Selection und Activate. Man sieht also wie das Programm arbeitet und es ist sehr langsam. Ich weiß aber, dass man auch den Wert einer Zelle lesen und schreiben kann indem man mit Variablen arbeitet. Mein Problem ist, wie ich die Variable für die Zelle um 1 nach unten verschieben kann.

    Allgemeine Info: Ich habe eine .xls mit mehreren Blättern. Diese kopiere ich nacheinander in die geöffnete .xls. Dann gehe ich jede Zelle in Spalte A durch und lösche alle Zeilen, die nicht dem Kriterum entsprechen. Das ist mein Ansatz. Vielleicht gibst aber auch andere Möglichkeiten.

    Schon mal Danke für die Antworten

    Gruß

    FeliX_22

  • Nahdem du das eh halbautomatisch machst - was spricht gegen einen Autofilter und nur das rauskopieren was du auch haben willst? Falls die Prüfung zu komplex ist einfach eine Spalte zusätzlich einfügen und nach der filtern! (bzw. kannst du ja auch umgekehrt alles löschen was nicht passt - einfach als makro aufnehmen, das sollte passen)

  • Hallo Oktabi!

    Erstmal Danke für deine Antwort. Manuell reicht da der AutoFilter und die Prüfung ist auch sehr einfach, aber es soll ja vollautomatisch passieren nachdem ich eine bestimmte Zelle ändere. Vielleicht war mein vorheriger Post nicht eindeutig. Deshalb hier nochmal der richtige Quelltext.

    Die Methode mit Zellen auszuwählen sähe so aus:

    Quellcode

    1. ActiveSheet.Range("A2").Select ' Gehe zu A2 (weil A1 die Überschrift trägt)
    2. Do
    3. If Selection.Value = Variable Then ' Entweder
    4. ActiveCell.Offset(1, 0).Range("A1").Select ' Gehe eine Zelle runter
    5. Else ' Oder
    6. Selection.EntireRow.Delete ' Ganze Zeile löschen, danach wird automatisch in die nächste Zeile gesprungen
    7. End If
    8. Loop Until Selection.Value = "" ' Wiederhole bis Zelle leer ist


    Die richtige Methode:

    Quellcode

    1. Set curCell = Worksheets("Sheet1").Cells(2, 1) ' Variabale curCell trägt die Zelle A2
    2. Do
    3. If curCell.Value = Variable Then ' Entweder
    4. curCell = curCell.Offset(1, 0).Range("A1") ' Eine Zeile runter /* FEHLER */
    5. Else ' Oder
    6. curCell.EntireRow.Delete ' Ganze Zeile löschen /* FEHLER */
    7. End If
    8. Loop Until curCell.Value = "" ' Wiederhole bis Zelle leer ist



    Da wo /* FEHLER */ steht muss natürlich was richtiges hin. Aber was??? Ich erhalte den Laufzeitfehler '424': Objekt erforderlich. Kann mir da bitte jemand weiterhelfen?

    thx

    FeliX_22

  • Also FeliX_22,

    wenn ich mir den Code so ansehe, dann willst du beginnend in A2, endend in der ersten leeren Zeile Spalte A alle Zeilen komplett löschen, die einen bestimmten Wert (Zahl oder Text ist egal) haben. Richtig?

    Dann wird dieser Code besser hinhauen:

    Quellcode

    1. Sub ZeilenLöschen()
    2. Dim Ze As Integer
    3. Dim Vergleich As Variant
    4. Dim Ws As String
    6. Vergleich = 25 'Alle [I]25 [/I]sollen gelöscht werden
    7. Ws = "Sheet1" 'Oder wie das Arbeitsblatt heißt
    8. Worksheets(Ws).Select
    9. Cells(2, 1).Select 'Erste Zeile
    10. Selection.End(xlDown).Select 'Letzte Zeile vor Leerzeile
    11. Do
    12. Ze = ActiveCell.Row
    13. If Ze = 1 Then Exit Do
    14. If Cells(Ze, 1) = Vergleich Then
    15. Selection.EntireRow.Delete
    16. End If
    17. Cells(Ze - 1, 1).Select
    18. Loop
    19. End Sub
    Alles anzeigen

    Try and enjoy!
    Internette Grüße
    von der Nautilus
    cpt. Nemo

  • Bitte nicht Select - so rufst du den Laufzeitteufel herbei! (drei mal hintereinander, da muss er ja kommen und dir die Augen auskratzen...)
    Wenn es irgendwie geht muss immer Select verhindert werden - das braucht nur unnötige Ressourcen. Und eingebauten funktionen (wie der Autofilter) sind sowieso immer sehr schnell.

    Bitteschön, das sollte die Aufgabe sehr schnell erledigen:

    Quellcode

    1. Sub Makro1()
    2. 'Eigentlich muss das nicht sein - aber der Bildschrim soll nicht flackern
    3. Application.ScreenUpdating = False
    5. 'Kriterium muss angepasst werden...
    6. ActiveSheet.Range("A1").AutoFilter Field:=1, Criteria1:=">2.5", Operator:=xlAnd
    7. 'Titelzeile verstecken - wenn man sie nicht sieht, wird sie im naechsten Schritt nicht geloescht
    8. ActiveSheet.Range("A1").EntireRow.Hidden = True
    9. 'Alle sichtbaren loeschen...
    10. ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).EntireRow.Delete Shift:=xlUp
    11. 'R1 wieder Zeigen
    12. ActiveSheet.Range("A1").EntireRow.Hidden = False
    13. 'und wieder weg mit dem Filter
    14. ActiveSheet.Range("A1").AutoFilter
    15. 'ja - hier ist ein Select - Sonst haengt der Focus irgendwo ...
    16. ActiveSheet.Range("A1").Range("A1").Select
    18. 'Muss auch nicht sein, ist aber sauberer ^^
    19. Application.ScreenUpdating = True
    20. End Sub
    Alles anzeigen


    (na OK - mit dem makro-Rekorder komst du nicht auf sowas :) )

    Ohne Kommentare ist das ganze auch nur halb so lang ^^

    Jeder der will kann die Laufzeiten vergleichen (und darf sich nicht wundern was der Laufzeitteufel beim (manuellem) Vergleich von 40k Zeilen macht)

  • Hallo!

    Vielen Dank für eure Posts!

    Ich hab die letzte Nacht durchgemacht und bin endlich fündig geworden. In der Hilfe stand was von Cells.Find. Mein Code durchsucht jetzt mit einer Variablen jede Zelle in Spalte A und wenn der Wert gefunden wird, wird die Zeile gelöscht.

    Zu Selection:
    Ja das dauert immer viel zu lange, deshalb wollte ich ja die Variablen.

    Zu ActiveSheet.Range("A1").AutoFilter:
    Du schreibst Criteria1:=">2.5". Das hatte ich auch probiert, aber was machst du wenn du das Kriterium aus ner Variablen auslesen musst? Ich habe Criteria1:="=" & Variable probiert, aber das ging nicht. (Ich brauche nicht > sondern = )

    Einen Stolperstein, den ich mehrmals hatte war, dass ich vor Range oft ActiveSheet. angeben musste, obwohl das in der Hilfe nicht so stand. Im Allgemeinen finde ich die VBA Hilfe in Excel 2007 nicht so gut, weil ich länger brauche um zur Lösung zu kommen, als in früheren Versionen.

    @cpt. Nemo
    Genial! Einfach in die letzte Zeile springen und dann nach oben wandern. Darauf muss man erst mal kommen!! :D

    @Oktabi
    Ich würde gern mal deinen Code ausprobieren, sieht irgendwie schlanker aus, als meine Lösung. Und ist auch garantiert schneller, als jede Zelle zu durchsuchen. Aber wie gesagt, das mit der Variablen beim Kriterium muss anders gehen. Hast du da ne Idee?

    Bei Interesse poste ich hier meine Lösung, habe sie aber gerade nicht zur Hand.

    :danke:

    FeliX_22

  • Eigentlich ist es ja trivial ^^ du musst nur den String anpassen, wie der zu stande kommt ist egal. Ich bevorzuge es das ganze Ding in einer Variablen zu haben, das ist übersichtlicher. Vielleicht war nicht ganz klar, dass dieser Ansatz nach allem sucht was gelöscht werden soll, d.h du definierst was du brauchst und machst ein ungleich daraus - das soll der Autofilter ja verbergen!

    Wie gesagt, einzlne Zellwerte zu prüfen ist ein schlechter Plan. Wenn es nur um 2k Zeilen geht ist es wurscht was du verwendest (solange es terminiert ^^) - wenn du aber deine vollen 65.536 Zeilen anschaust kann es schnell langweilig werden - vor allem wenn du nach zwei oder mehreren Spalten filtern willst...

    Quellcode

    1. Sub Makro1()
    2. ' Das Kriterium muss jene Zellen treffen die geloescht werden sollen, z.B nur Donnerstage behalten:
    3. Dim kriterium
    4. kriterium = "<>Do"
    6. 'Eigentlich muss das nicht sein - aber der Bildschrim soll nicht flackern
    7. Application.ScreenUpdating = False
    9. 'Kriterium muss angepasst werden...
    10. ActiveSheet.Range("A1").AutoFilter Field:=1, Criteria1:=kriterium, Operator:=xlAnd
    11. 'Titelzeile verstecken - wenn man sie nicht sieht, wird sie im naechsten Schritt nicht geloescht
    12. ActiveSheet.Range("A1").EntireRow.Hidden = True
    13. 'Alle sichtbaren loeschen...
    14. ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).EntireRow.Delete Shift:=xlUp
    15. 'R1 wieder Zeigen
    16. ActiveSheet.Range("A1").EntireRow.Hidden = False
    17. 'und wieder weg mit dem Filter
    18. ActiveSheet.Range("A1").AutoFilter
    20. 'Muss auch nicht sein, ist aber sauberer ^^
    21. Application.ScreenUpdating = True
    23. 'ja - hier ist ein Select - Sonst haengt der Focus irgendwo ...
    24. ActiveSheet.Range("A1").Select
    25. End Sub
    Alles anzeigen

  • Hallo!

    Um den Thread abzuschließen poste ich hier meine Lösung. Sie ist wie schon gesagt im Prinzip von der VBA-Hilfe in Excel.

    Quellcode

    1. Private Sub ZeilenKopieren()
    2. With Worksheets("Tabelle2").Range("A1:A65536")
    3. Set c = .Find(Suchstring, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
    4. If Not c Is Nothing Then
    5. firstAddress = c.Address
    6. Do
    7. i = i + 1
    8. c.EntireRow.Copy Destination:=Worksheets("Tabelle1").Cells(i, 1)
    9. Set c = .FindNext(c)
    10. Loop While Not c Is Nothing And c.Address <> firstAddress
    11. End If
    12. End With
    13. End Sub
    Alles anzeigen


    Jede Zelle wird nach einem String durchsucht und dann in ein anderes Arbeitsblatt kopiert. Dabei wird die Ziel-Zelle mithilfe von i immer um eine Zeile nach unten verschoben.

    Dass ich glücklicherweise "A1:A65536" angeben kann, liegt daran, dass der Nutzer Excel 2003 benutzt, denn im 2007er gibt es wesentlich mehr Zeilen und Spalten. Natürlich wäre AutoFilter besser. Denn es dauert schon paar Sekunden bis alle etwa 20000 beschriebenen Zeilen abgearbeitet sind. Das Projekt ist jetzt aber größer geworden. Wenn ich später noch Zeit habe versuche ich vielleicht den AutoFilter zu nutzen.

    Danke für eure Tipps!

    Schöne Grüße

    FeliX_22