Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1832to1836
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
Inhaltsverzeichnis

Nach Ablaufdatum Zeile löschen

Nach Ablaufdatum Zeile löschen
07.06.2021 13:01:54
Marco
Hallo
Ich bin neu in VBA. Ich habe aber etwas Erfahrung in VBS.
In meiner Tabelle gibt es eine Spalte Datum. Mehrere Zeilen können das gleiche Datum beinhalten. Sobald ich die Arbeitsmappe öffne, sollen die Datensätze (Zeilen), die älter als 20 Tage sind, automatisch löscht werden. Außerdem sollen Datensätze ab den fünften Tag besonders gekennzeichnet werden, wenn bestimmte Spalten leer sind. Mit der Löschfunktion habe ich hier meine Schwierigkeiten und weiß keinen Rat mehr.
Was musst ich tun, nachdem das Makro die gesamte Zeile gelöscht hat?
Vielen Dank im Voraus.
Hier ist mein Versuch.

Option Explicit
Sub test()
Const LoeschenNach As Integer = 20
Const MeldenNach As Integer = 5
Dim Eingangsdatum As Date
Dim Heute As Date
Dim SuchBereich As Range
Dim IstDatum As Boolean
Dim i As Long
Dim j As Long
Heute = Date
' Bildschirmaktualisierung deaktivieren (Beschleunigt die Makroauführung)
Application.ScreenUpdating = False
' Verwendete Zeilen zählen
i = ThisWorkbook.ActiveSheet.UsedRange.Rows.Count
' Den zu durchsuchende Bereich festlegen
Set SuchBereich = ThisWorkbook.ActiveSheet.Range("I1:I" & i)
'   I   |     J     |    K
' Datum | E-Mail am | Brief am
' Durch den Suchbereich iterieren
For j = 1 To SuchBereich.Count
' Fehlerbehandlung aktivieren
On Error Resume Next
IstDatum = False
' Kann der Zellenwert in "Date" konvertiert werden?
Eingangsdatum = CDate(SuchBereich(j).Value)
If Err.Number = 0 Then
IstDatum = True
End If
' Fehlerbehandlung deaktivieren
On Error GoTo 0
If IstDatum Then
' Meldung nach fünf Tage verschicken!
If (DateDiff("d", Eingangsdatum, Heute) > MeldenNach) Then
If IsEmpty(Range("J" & j).Value) And IsEmpty(Range("K" & j).Value) Then
Range("J" & j & ":K" & j).Interior.Color = vbYellow
Range("J" & j).Value = "Bitte E-Mail versenden!"
Range("K" & j).Value = "Bitte Brief verschicken!"
End If
End If
' Datensatz nach 20 Tagen löschen.
If (DateDiff("d", Eingangsdatum, Heute) > LoeschenNach) Then
SuchBereich(j).EntireRow.Delete
' j = j - 1 !!! ENDLOSSCHLEIFE !!!
End If
End If
Next j
' Bildschirmaktualisierung aktivieren
Application.ScreenUpdating = True
End Sub

2
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Nach Ablaufdatum Zeile löschen
07.06.2021 13:15:15
Daniel
Hi
Schleifen die Zeilen löschen oder einfügen sollten immer rückwärts laufen, von der letzten bis zur ersten Zeile.

For j = SuchBereich.Count to 1 Step - 1
Dann spielt sich die Veränderung in dem Bereich ab, den die Schleife schon durchlaufen hat und hat keinen Einfluss mehr.
Dann musst du auch nicht den Schleifenzähler manipulieren.
Würdest du es anders machen, müsstest du beim Löschen einer Zeile nicht nur den Schleifenzähler um 1 reduzieren, sondern auch den Schleifenendwert, sonst läuft die Schleife am Ende noch über die nachrückenden Leerzeilen, und die werden bei deiner Prüfung immer gelöscht.
Daher dann die Endlosschleife.
Die Manipulation des Schleifenendwertes ist jedoch bei einer For-Next- Schleife nicht möglich, dazu braucht es dann ein Do-Loop.
Aber wie gesagt, Schleife rückwärts laufen lassen und du hast diese Probleme nicht.
Gruß Daniel
Anzeige
AW: Nach Ablaufdatum Zeile löschen
07.06.2021 21:15:36
Yal
Hallo Marco,
zwar hat Daniel die Frage beantwortet, aber da Du willig scheinst, Dich in VBA zu vertiefen, erlaube ich mir meine Version vorzuschlagen (Klugscheisser-Modus an :-)

Sub test()
Const LoeschenNach As Integer = 20
Const MeldenNach As Integer = 5
Dim Eingangsdatum As Date
Dim SuchBereich As Range
Dim i As Long
Application.ScreenUpdating = False
With ThisWorkbook.ActiveSheet
Set SuchBereich = .Range("I1:I" & .UsedRange.Rows.Count)
'Alternativ
'    Set SuchBereich = Intersect(.Range("I:I"), .UsedRange)
End With
On Error Resume Next
For i = SuchBereich.Cells.Count To 1 Step -1
Eingangsdatum = 1 'Default-Wert setzen
Eingangsdatum = CDate(SuchBereich(i).Value)
If Eingangsdatum  1 Then 'wenn Default-Wert erfolreich geändert wurde
Select Case DateDiff("d", Eingangsdatum, Date)
Case Is > LoeschenNach ' Datensatz nach 20 Tagen löschen.
SuchBereich(i).EntireRow.Delete
Case Is > MeldenNach ' Meldung nach fünf Tage verschicken!
Formatieren SuchBereich(i)
End Select
End If
Next i
Application.ScreenUpdating = True
End Sub
Private Sub Formatieren(ZielZelle As Range)
With ZielZelle
If (.Offset(0, 1) & .Offset(0, 2)) = "" Then
.Range("B1:C1").Interior.Color = vbYellow
.Range("B1").Value = "Bitte E-Mail versenden!"
.Range("C1").Value = "Bitte Brief verschicken!"
End If
End With
End Sub
Dazu ein paar Erklärungen:
_ wie von Daniel gesagt: löschen ist wie Boden nasswischen: von hinten zu Ausgangstür hin. Hier mit den umgekehrten Wert und Step -1 im For-Initialisierung.
_ Variable-Sparsamkeit ist empfohlen, wobei im Debugging-Phase Zwischenvariable helfen, im Schritt-Nodus bei geöffnete Lokal-Fenster ("Ansicht") zu nachvollziehen, was passiert.
_ IstDatum wird gespart, in dem man eine Defaultwert verwendet, die nur geändert wird, wenn die Zelle eine Datum hat. Alternativ, da Eingangsdaten ausserhalb der gegebenen Grenzen keine Auswirkung haben, könnte man das heutige Datum als Default-Wert verwenden und die Prüfung der Änderung sparen (Siehe zweiten Code)
_ eine mehrfache, einseitige Prüfung ist übersichtlicher mit Select Case (Geschmacksache, zugegeben). Reihenfolge der Prüfung ist wichtig: > 20 muss vor > 5 sein. Bei "Case Is = 3" kann man in "Case 3" kürzen.
_ daraus wird "Heute" nur einmal verwendet. Man kann dann direkt Date aufrufen.
_ On Error mag ich allgemein nur einmal pro Sub/Function und idealerweise nicht innerhalb eine Schleife (ok, auch da: Geschmacksache)
_ On Error gilt nur für die aktuelle Sub/Function. Für ein anderen On Error-Stufe kann/sollte man eine separate Sub/Function verwenden
_ im allgemein: so wenig wie möglich innerhlab der Schleife (Performance, trifft aber hier nicht zu)
_ um die Wiederholung langer Informationskette (ThisWorkbook.ActiveSheet) oder haufige Wiederholung (ZielZelle) zu vermeiden, kann man den "With ... End With" nutzen. Der Punkt vor Range und UsedRange deutet auf dem With-Verweis. Wenn ein With auftaucht, sollte den End With auch nicht zu weit sein.
_ bei einzelnen Zelle, wenn es geht, mit den Range-Objekt arbeiten. Dann kann man nicht nur EntireRow sondern auch Offset, Resize oder relative Adresse verwenden: Range("B2").Range("B2:D2").Address ergibt $C$3:$E$3 (!)
Ansonsten für ein "Anfänger" war dein Code sauber und gut dokumentiert. Respekt. (wichtig, wenn man in 3 Jahre es wieder verstehen möchte. Bei mir manchmal schon nach 3 Tage... :-(
Version Default-Wert Eingangsdatum auf heute:

For i = SuchBereich.Cells.Count To 1 Step -1
Eingangsdatum = Date 'Default-Wert setzen
Eingangsdatum = CDate(SuchBereich(i).Value)
Select Case DateDiff("d", Eingangsdatum, Date)
Case Is > LoeschenNach ' Datensatz nach 20 Tagen löschen.
SuchBereich(i).EntireRow.Delete
Case Is > MeldenNach ' Meldung nach fünf Tage verschicken!
Formatieren SuchBereich(i)
End Select
Next i
Nun: Klugscheisser-Modus aus!
VG
Yal
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige