Microsoft Excel

Herbers Excel/VBA-Archiv

Liste überprüfen

Betrifft: Liste überprüfen von: Gig
Geschrieben am: 16.10.2020 12:12:50

Hallo,

ich möchte mittels VBA bei eine Liste mit ca 8000 Datensätzen (Zeilen) überprüfen, ob das Datum (Spalte B) innerhalb eines bestimmten Datumbereichs (z.B. 01.11.2020 - 31.12.2020) liegt. Liegt das Datum vor dem 1.11 bzw nach dem 31.12 dann soll die gesamte Zeile gelöscht werden.

Hat dazu jemand eine Idee?
Oder gibt es dafür auch eine Formellösung?

Vielen Dank im Voraus!

https://www.herber.de/bbs/user/140912.xlsx

Betrifft: AW: Liste überprüfen
von: Pierre
Geschrieben am: 16.10.2020 12:49:25

Hallo,

ich glaube nicht, dass man per Formel Zeilen löschen kann...

Hier mal eine Version, bei der der Datumsbereich fest vorgeschrieben ist:
Sub ZeileLoeschenWennDatumKleinerGroesser()

Dim loeschen As Double

For loeschen = Cells(Rows.Count, 2).End(xlUp).Row To 1 Step -1
    If Cells(loeschen, 2).Value < CDate("01.11.2020") Or _
    Cells(loeschen, 2).Value > CDate("31.12.2020") Then
        Rows(loeschen).Delete                                    
    End If
Next loeschen

End Sub
Gruß

Betrifft: AW: Liste überprüfen
von: Gig
Geschrieben am: 16.10.2020 13:17:21

Hallo,
danke für deine Idee
Das Makro funktioniert einwandfrei. Aber es benötigt für knapp 8300 Datensätze beinahe 4 Minuten!
Das ist mir eindeutig zu lange.

ad Formel
Könnte man mit einer Formel den Inhalt einer Zeile löschen oder zumind. den Text aus Spalte A?

LG

Betrifft: AW: Liste überprüfen
von: Pierre
Geschrieben am: 16.10.2020 14:18:44

Okay, das konnte und wollte ich jetzt nicht wirklich testen. Bei kleineren Datenmengen ist das ja irrelevant.

Betrifft: AW: Liste überprüfen
von: Oberschlumpf
Geschrieben am: 16.10.2020 12:50:48

Hi,

MUSS es VBA sein?
Einfach geht es auch mit dem Autofilter.

Füg eine Überschriftenzeile hinzu, wenn noch nicht vorhanden
Markier eine Zelle in der Überschriftenzeile und aktivier den Autofilter
Setz als Bedingung in Spalte B den gewünschten Zeitraum
Jetzt nur die sichtbaren Zeilen löschen - alles ist gut

Ciao
Thorsten

Betrifft: AW: Liste überprüfen
von: Gig
Geschrieben am: 16.10.2020 13:01:00

Hallo,
danke für deine Idee mit dem Autofilter.
Das Problem ist aber: Ich möchte anschliesend mit dieser Tabelle weiterarbeiten und mittels SVerweis darauf zugreifen. Ich denke, hier wird es ein Problem geben...

Betrifft: AW: Liste überprüfen
von: Oberschlumpf
Geschrieben am: 16.10.2020 13:06:44

Hi,

hast du es denn mit Autofilter versucht?!

Wenn mit Autofilter die Zeilen gelöscht sind, löschst du wieder die Autofilter-Bedingungen - es werden wieder alle übrigen Zeilen angezeigt.

Und ob nun mit oder ohne Autofilter, das könnte dem SVerweis egal sein.

Ciao
Thorsten

Betrifft: AW: Liste überprüfen
von: Gig
Geschrieben am: 16.10.2020 13:24:12

Die Zeile werden mit dem Autofilter nicht gelöscht. Sie sind nur nicht sichtbar. SVerweis findes sie trotzdem. ....

Betrifft: AW: Liste überprüfen
von: Daniel
Geschrieben am: 16.10.2020 15:20:59

Hi

Du musst mit dem Autofilter alle Zeilen, die weiterhin benötigt werden, ausblenden und dann alle Zeilen löschen.
In gefterten Listen werden nur die sichtbaren Zeilen gelöscht, die ausgeblendeten bleiben erhalten und sind wieder da, wenn du den Filter aufhebst.

Den Formeln ist egal, ob du Zeilen ausgeblendet hast oder nicht, die werten immer alles aus.

Gruß Daniel

Betrifft: PQ erzeugt hingegen eine "netto"-Liste ;-)
von: Günther
Geschrieben am: 16.10.2020 15:48:04

OwT
 
Gruß
Günther  |  mein Excel-Blog

Betrifft: AW: Liste überprüfen
von: Daniel
Geschrieben am: 16.10.2020 13:27:09

Hi
Zeilen Löschen geht am schnellsten und einfachsten damit, dass man die Zeilen, die gelöscht werden sollen in einer Hilfsspalte per Formel mit 0 kennzeichnet und die die übrig bleiben sollen mit der Zeilennummer.
in die Überschriftenzeile der Hilfsspalte kommt ebenfals die 0.
dann wendet man DATEN - DATENTOOLS - DUPLIKATE ENTFERNEN an, mit der Hilfsspalte als Kriterium und er Option "keine Überschrift"
danach kann man die Hilfsspalte löschen.

sieht als VBA-Code so aus:
With ActiveSheet.UsedRange
    With .Columns(.Columns.Count + 1)
        .FormulaR1C1 = "=IF(OR(RC2Date(2020,12,31)),0,Row())"
        .Cells(1, 1).Value = 0
        .entireRow.Removeduplicates .column, xlno
        .clearContents
    end with
end with

Gruß Daniel

Betrifft: AW: Liste überprüfen
von: gig
Geschrieben am: 16.10.2020 14:55:53

Hallo Daniel,
Danke für deine Hilfe.
Aber mit deiner Formel stimmt irgendetwas nicht. Ich denke, es ist das if-statement.


LG

Betrifft: AW: Liste überprüfen
von: Daniel
Geschrieben am: 16.10.2020 15:17:05

Hi

Da hats wohl was aus versehen gelöscht.

Die Formel solltest du dir aber mit Hilfe meiner Beschreibung selber herleiten können.

Sie sollte im Prinzip folgendes ausgeben:
Wenn Datum in Spalte B kleiner als 1.11.2020 oder Datum in Spalte B größer als 31.12.2020, dann 0 sonst die Zeilennummer (Zeile()).

Wenn du die Formel normal in Excel erstellt hast, kannst du die Schreibweise für VBA leicht mit dem Makrorecorder herausfinden, oder du lässt sie dir im Direktfenster mit
?Selection.FormulaR1C1
anzeigen und übernimmst sie von dort in den Code.

Gruß Daniel

Betrifft: AW: Liste überprüfen
von: gig
Geschrieben am: 16.10.2020 16:05:22

Danke Daniel.
Deine Lösung gefällt mir am besten!

Betrifft: AW: Liste überprüfen
von: Günther
Geschrieben am: 16.10.2020 14:16:08

Moin,
mit deiner Version: Daten | Abrufen und transformieren und dann den Datumsfilter verwenden.
 
Gruß
Günther  |  mein Excel-Blog

Betrifft: AW: Liste überprüfen
von: gig
Geschrieben am: 16.10.2020 14:58:29

Hallo,
Danke für deine Antwort.
Kannst du etwas genauer werden?. Kann nicht folgen..

Betrifft: AW: Liste überprüfen
von: Günther
Geschrieben am: 16.10.2020 15:08:35

Moin, gerne ...
In deiner Version ist Power Query über den Weg Daten | Abrufen und transformieren integriert. Ich habe deine Datei auf 100.000 Datensätze ausgeweitet und kam bei einer Aktualisierung der Daten in (gefühlt) <0,25 sec. zum Ergebnis. Ein Beispiel (auch) für Power Query findest du hier: http://www.excel-ist-sexy.de/zeilen-eines-zeitlichen-bereichs-loeschen/
 
Gruß
Günther  |  mein Excel-Blog

Betrifft: AW: Liste überprüfen
von: gig
Geschrieben am: 16.10.2020 16:06:14

Danke für deinen Vorschlag!