Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1740to1744
1740to1744
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Datum Filtern

Forumthread: Datum Filtern

Datum Filtern
13.02.2020 10:08:22
Dome
Hi Ihr lieben,
ich habe eine Excel-Tabelle mit mehr wie 50.000 Zeilen. In diesen Zeilen werden verschiedenste Daten (Mehrzahl von Datum) eingetragen.
Nun benötige ich ein Makro, welches mir aus den über 50.000 Zeilen das gestrige Datum herausfiltert. Warum ich nicht die normale Filter-Funktion nutze? Dauert zu lange und ich muss insgesamt 10 Filter setzten (diese Funktionieren - bis auf das Datum).
Hier mein aktuelles Makro, was funktioniert (möchte aber nicht jeden Tag das Makro anpassen müssen):
ActiveSheet.Range("A1:A50000").AutoFilter Field:=1, Criteria1:=Array( _
"12.02.2020"), Operator:=xlFilterValues
Makro welches nicht funktioniert:
ActiveSheet.Range("A1:A50000").AutoFilter Field:=1, Criteria1:=Array( _
"date.now()-1"), Operator:=xlFilterValues
Wo ist der Fehler?
Danke
Dominic
Anzeige

19
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Datum Filtern
13.02.2020 10:21:23
Torsten
Hi Dominic,
versuch mal: ActiveSheet.Range("A1:A50000").AutoFilter Field:=1, Criteria1:=CLng(Date) - 1, Operator:=xlFilterValues
Gruss Torsten
AW: Datum Filtern
13.02.2020 11:40:01
Dome
Funkt leider nicht :( Laufzeitfehler 1004
AW: Datum Filtern
13.02.2020 10:25:43
JoWE
Hallo Dominic,
vllt. so:
ActiveSheet.Range("A1:A50000").AutoFilter Field:=1, _
Criteria1:=Array(Date - 1), Operator:=xlFilterValues
Gruß
Jochen
Anzeige
AW: Datum Filtern
13.02.2020 11:40:11
Dome
Funkt leider nicht :( Laufzeitfehler 1004
AW: bitte Beispieltabelle hochladen
13.02.2020 12:07:35
JoWE
AW: bitte Beispieltabelle hochladen
13.02.2020 13:14:54
Dome
Soweit ich sehe, funktioniert es jetzt
Sub Heute()
' Heute Makro
ActiveSheet.Unprotect "xxxxx"
ActiveSheet.Range("A1:A50000").AutoFilter Field:=1, Criteria1:=Array( _
Format(Date - 1, "DD.MM.YYYY")), Operator:=xlFilterValues
ActiveSheet.Range("$A$1:$R$45631").AutoFilter Field:=17, Criteria1:=Array( _
"xxx", "xxx", "xxx", "xxx", "xxx", _
"xxx", "xxx", "xxx", "xxx", _
"xxx", "xxx", "="), Operator:=xlFilterValues
ActiveSheet.Protect "xxx"
End Sub

Jetzt ist nur das Problem, dass nach anwenden des Makros die Excel ja wieder Passwort geschützt wird (ist ja auch richtig so) aber man kann die durch das Makro gesetzten Filter nicht mehr herausnehmen. Um dies zu tun, muss man den Blattschutz aufheben.
Kann ich dem Makro sagen, dass wenn er am Ende wieder das Passwort setzt, die Funktion "AutoFilter verwenden" außer acht lässt (ist ja bein normalen Blattschutz nur durch einen Haken auswählbar.
Background:
Es geht um eine Verkaufsliste. In diese Liste tragen 8 Mitarbeiter Ihre Verkäufe ein (deswegen auch die 50.000 Zeilen (jeder Mitarbeiter hat seinen eigenen Bereich, den er Bearbeiten kann)), damit Herr Meier nicht den Bereich von Hr. Müller bearbeiten kann, habe ich dies in Bereiche eingegliedert und mit Passwort versehen. Nun muss ich am Folgetag mir genau diese Verkaufe ziehen (deswegen auch heutiges Datum-1). Funktioniert ja jetzt auch, aber dann wird die Liste wieder durch ein PW versehen und ich kann die gesetzten Filter nicht mehr herausnehmen, da durch das Makro auch der Filter PW-geschützt wird - wie änder ich das?
MfG.
Dominic
Anzeige
AW: ActiveSheet.AutoFilter.ShowAllData
13.02.2020 13:27:10
JoWE
AW: ActiveSheet.AutoFilter.ShowAllData
13.02.2020 13:45:11
Dome
Hallo Jochen,
Danke - hat alles geklappt.
Letztes Problem..
Das Makro funktioniert nicht im geteilten Modus (Arbeitsmappe teilen (Legacy)). Ist die Arbeitsmappe nicht geteilt, funkt alles einwandfrei - teile ich diese, kommt der Fehler "Langzeitfehler 1004".
Frage: Ist es möglich bei datum-1 auch das Wochenende zu beachten? Das ich mir quasi am Monat die Verkäufe vom Freitag öffne?
Wenn nicht, ist nicht so wild
Anzeige
AW: ActiveSheet.AutoFilter.ShowAllData
13.02.2020 14:21:48
fcs
Hallo Dominic,
folgende Anpassung, um Montags die Werte von Freitag zu filtern: 
    ActiveSheet.Range("A1:A50000").AutoFilter Field:=1, Criteria1:=Array( _
Format(Date - IIf(Weekday(Date, 2) = 1, 3, 1), "DD.MM.YYYY")), _
Operator:=xlFilterValues
LG
Franz
Anzeige
AW: ActiveSheet.AutoFilter.ShowAllData
13.02.2020 15:41:49
Dome
Perfekt, danke dir Franz.
Wie kann ich as Makro jetzt im geteilten Modus nutzen?
AW: ActiveSheet.AutoFilter.ShowAllData
13.02.2020 17:46:29
fcs
Hallo Dominic,
im Freigegebenen Arbeitsmappen (Frühere Freigabe-Methode) sind verschiedenen Features nicht unterstützt. Unter Hilfefunktion findet man hierzu:
 AB
2Beachten Sie, dass freigegebene Arbeitsmappen ein älteres Verfahren für die Freigabe sind. Zahlreiche Elemente und Aktionen werden daher nicht unterstützt, wenn diese Methode verwendet wird. In der folgenden Tabelle wird nur ein Teil der nicht unterstützten Features aufgeführt. 
3Nicht unterstützte Elemente:Nicht unterstützter Aktionen:
4Erstellen oder Einfügen von TabellenEinfügen oder Löschen von Zellenblöcken
5Hinzufügen oder Ändern bedingter FormatierungenLöschen von Arbeitsblättern
6Hinzufügen oder Ändern der DatenüberprüfungVerbinden von Zellen oder Aufteilen verbundener Zellen
7Erstellen oder Ändern von Diagrammen oder PivotChart-BerichtenSortieren oder Filtern nach Format
8Einfügen oder Ändern von Bildern oder anderen ObjektenVerwenden von Zeichentools
9Einfügen oder Ändern von LinksZuweisen, Ändern oder Entfernen von Kennwörtern
10Erstellen, Ändern oder Anzeigen von SzenarienSchützen oder Aufheben des Schutzes von Arbeitsblättern oder der Arbeitsmappe
11Einfügen von automatisch erstellten TeilergebnissenGruppieren oder Gliedern von Daten
12Erstellen von DatentabellenSchreiben, Aufzeichnen, Ändern, Anzeigen oder Zuweisen von Makros
13Erstellen oder Ändern von PivotTable-BerichtenÄndern oder Löschen von Matrixformeln
14Erstellen oder Anwenden von DatenschnittenHinzufügen, Umbenennen oder Löschen von XML-Zuordnungen
15Erstellen oder Ändern von SparklinesZuordnen von Zellen zu XML-Elementen
16Hinzufügen oder Ändern von Dialogblättern aus Microsoft Excel 4Verwenden des Aufgabenbereichs "XML-Quelle", der XML-Symbolleiste oder von XML-Befehlen im Menü "Daten"
17 Hinzufügen neuer Daten in einem Datenformular
18Hinzufügen von Gewinde KommentarenBearbeiten oder Löschen von Gewinde Kommentaren


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Dein Problem ist hier der Blattschutz.
Du kannst die Datei aber so schützen, dass ein aktiver Autofilter weiterhin bedient werden kann.
Allerdings weiss ich nicht ob man dann den Filter per Makro ändern kann.
Aber sind es nur 3 bis 5 Klicks im Dropdown--&gtDatumsfilter--%gt "gestern" oder "ist gleich" und im Kalender den Freitag wählen.
LG
Franz
Anzeige
AW: ActiveSheet.AutoFilter.ShowAllData
14.02.2020 09:27:23
Dome
Aber warum geht denn dieses Makro im geteilten Modus?
Sub Marcel()
' Marcel Makro
' Marcel
' Tastenkombination: Strg+Umschalt+M
ActiveWindow.SmallScroll Down:=99
Range("A100").Select
End Sub

AW: ActiveSheet.AutoFilter.ShowAllData
14.02.2020 09:34:38
Dome
Ich habe hier im Forum diesen Befehl gefunden:
Sub Makro_mit_Freigabe_ausführen()
ActiveWorkbook.ExclusiveAccess
'Mein Makro
If Not ActiveWorkbook.MultiUserEditing Then
ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.FullName, _
AccessMode:=xlShared
End If
End Sub
Jedoch kommt die Fehlermeldung
Die Methode "ExclusiveAccess" für das Objekt "_Workbook" ist fehlgeschlagen
Anzeige
AW: ActiveSheet.AutoFilter.ShowAllData
14.02.2020 09:44:07
Dome
Gibt es nicht einen Befehl wie bei unprotect/protect?
Also dass das Makro erst das teilen der Excel beendet, dann dass Passwort aufhebt - dann das eigentliche Marko ausführt, die Excel dann wieder sichert und zum Schluss wieder wieder teilt?
Hier mein aktuelles Marko:
Sub Heute()
' Heute Makro
ActiveWorkbook.ExclusiveAccess
ActiveSheet.Unprotect "xxx"
ActiveSheet.Range("A1:A50000").AutoFilter Field:=1, Criteria1:=Array( _
Format(Date - IIf(Weekday(Date, 2) = 1, 3, 1), "DD.MM.YYYY")), _
Operator:=xlFilterValues
ActiveSheet.Range("$A$1:$R$45631").AutoFilter Field:=17, Criteria1:=Array( _
"xxx", "xxx", "xxx", "xxx", "xxx", _
"xxx", "xxx", "xxx", "xxx", _
"xxx", "xxx", "="), Operator:=xlFilterValues
ActiveSheet.Protect "xxx", AllowFiltering:=True
If Not ActiveWorkbook.MultiUserEditing Then
ActiveSheet.SaveAs Filename:=xxx, _
AccessMode:=xlShared
End If
End Sub
Es funktioniert jetzt im geteilten Modus - jedoch setzt am Ende des Makros das Makro die Excel nicht zurück im geteilten Modus.
Wie gsesagt, das Makro 
Sub Marcel()
' Marcel Makro
' Marcel
' Tastenkombination: Strg+Umschalt+M
ActiveWindow.SmallScroll Down:=99
Range("A100").Select
End Sub
Kann ich im geteilten Modus ohne Probleme nutzen
Anzeige
AW: ActiveSheet.AutoFilter.ShowAllData
14.02.2020 11:18:17
fcs
Hallo Dominic,
das Scrollen gehört zu den zulässigen Aktionen in einer freigegebenen Arbeitsmappe.
Ich kann dir leider beim erneuten Freigeben der Mappe per Makro nicht helfen. Ich hab hier privat keine Testmöglichkeiten.
Ich kann Dateien nur nach OneDrive auslagen. Da funktioniert aber nur das neue Verfahren zum gemeinsamen Bearbeiten von Dateien - nicht mehr der Weg über Freigegeben Arbeitsmappen.
LG
Franz
Anzeige
AW: Datum Filtern
13.02.2020 10:26:31
fcs
Hallo Dominic,
versuche es mal so:

Sub FilternDatum_Filtervalues()
ActiveSheet.Range("A1:A50000").AutoFilter Field:=1, Criteria1:=Array( _
Format(Date - 1, "DD.MM.YYYY")), Operator:=xlFilterValues
End Sub
'Alternativ
Sub FilternDatum()
ActiveSheet.Range("A1:A50000").AutoFilter Field:=1, _
Criteria1:=Format(Date - 1, "DD.MM.YYYY"), Operator:=xlAnd
End Sub
LG
Franz
Anzeige
AW: Datum Filtern
13.02.2020 10:38:07
Luschi
Hallo Franz,
beide Versionen klappen bei mir mit E_2019 Pro+ 32-bit; aber dann verstehe ich nicht folgende _ Info von M$: 

Hinweis:
Bei Verwendung von AutoFilter mit Datumsangaben sollte das Format mit den englischen
Datumstrennzeichen ("/") und nicht mit den lokalen Einstellungen (".") übereinstimmen.
Ein gültiges Datum wäre "2/2/2007", während "2.2.2007" ungültig ist.
siehe: https://docs.microsoft.com/de-de/office/vba/api/excel.autofilter?redirectedfrom=MSDN
Gruß von Luschi
aus klein-Paris
Anzeige
AW: Datum Filtern
13.02.2020 14:36:56
fcs
Hallo Luschi,
wir wissen nicht, was im Hintergrund unter VBA passiert, wenn man mit Format(Date, "DD.MM.YYYY") arbeitet statt direkt den Datumstext einzugeben.
Ich bin auch überascht, dass es funktioniert hat. Ich hab es nur so probiert weil es mit dem Operator xlFilterValues und einem Array von Filterwerten im deutschen Format funktioniert.
Evtl. hat man es bei MS und deren Excel-Freaks nur noch nicht so probiert.
Ich hab dann noch experimentiert. Wenn das Datum im Tabellenblatt eine Sonderformatierung hat z.B. "TTT TT.MM" dann funktioniert der Autofilter unter VBA, wenn man im Kriterium das Datum genauso per Format formatiert.
Entweder hat MS da im Autofilter etwas verbessert oder es ist ein Zufallstreffer, dass es funktioniert.
LG
Franz
Anzeige
AW: Datum Filtern
13.02.2020 11:35:23
Dome
Danke Franz,
funkt leider auch nicht... Da kommt der Laufzeitfehler 1004
Userbild
;

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige