In diesem Tutorial wird die Verwendung der Advanced Filter-Methode in VBA erklärt
Die erweiterte Filterung in Excel ist sehr nützlich, wenn Sie mit großen Datenmengen arbeiten, bei denen Sie eine Vielzahl von Filtern gleichzeitig anwenden möchten. Es kann auch verwendet werden, um Duplikate aus Ihren Daten zu entfernen. Sie müssen mit dem Erstellen eines erweiterten Filters in Excel vertraut sein, bevor Sie versuchen, einen erweiterten Filter in VBA zu erstellen.
Betrachten Sie das folgende Arbeitsblatt.
Sie sehen auf einen Blick, dass es Duplikate gibt, die Sie möglicherweise entfernen möchten. Die Kontoart ist eine Mischung aus Sparen, Laufzeitdarlehen und Scheck.
Zuerst müssen Sie einen Kriterienbereich für den erweiterten Filter einrichten. Sie können dies in einem separaten Blatt tun.
Zur besseren Orientierung habe ich mein Datenblatt „Datenbank“ und mein Kriterienblatt „Kriterien“ genannt.
Erweiterte Filtersyntax
Expression.AdvancedFilter Action, CriteriaRange, CopyToRange, Unique
- Die Ausdruck stellt das Range-Objekt dar - und kann als Range eingestellt werden (zB Range(“A1:A50”) - oder der Range kann einer Variablen zugewiesen und diese Variable verwendet werden.
- Die Handlung Argument ist erforderlich und ist entweder xlFilterInPlace oder xlFilterCopy
- Die Kriterienbereich -Argument erhalten Sie die Kriterien, nach denen gefiltert werden soll (unseres Kriterienblatt oben). Dies ist optional, da Sie beispielsweise kein Kriterium benötigen, wenn Sie nach eindeutigen Werten filtern.
- Die CopyToRange Argument ist der Ort, an dem Sie Ihre Filterergebnisse ablegen - Sie können filtern oder Ihr Filterergebnis an einen anderen Ort kopieren lassen. Dies ist auch ein optionales Argument.
- Die Einzigartig Argument ist auch optional - Wahr besteht darin, nur nach eindeutigen Datensätzen zu filtern, Falsch besteht darin, nach allen Datensätzen zu filtern, die die Kriterien erfüllen - wenn Sie dies weglassen, ist die Standardeinstellung Falsch.
Filtern von Daten an Ort und Stelle
Anhand der oben im Kriterienblatt aufgeführten Kriterien möchten wir alle Konten mit einer Art von „Spar“ und „Current“ finden. Wir filtern vor Ort.
| 123456789 | Unter CreateAdvancedFilter()Dim rngDatabase As RangeDim rngCriteria As Range'Definieren der Datenbank- und KriterienbereicheSet rngDatabase = Sheets("Datenbank").Range("A1:H50")Set rngCriteria = Sheets("Criteria").Range("A1:H3")'Datenbank anhand der Kriterien filternrngDatabase.AdvancedFilter xlFilterInPlace, rngCriteriaEnd Sub |
Der Code blendet die Zeilen aus, die die Kriterien nicht erfüllen.
In der obigen VBA-Prozedur haben wir die Argumente CopyToRange oder Unique nicht eingeschlossen.
Zurücksetzen der Daten
Bevor wir einen weiteren Filter ausführen, müssen wir den aktuellen löschen. Dies funktioniert nur, wenn Sie Ihre Daten vor Ort gefiltert haben.
| 12345 | Unter ClearFilter()Bei Fehler Fortsetzen als nächstes'Filter zurücksetzen, um alle Daten anzuzeigenActiveSheet.ShowAllDataEnd Sub |
Filtern von eindeutigen Werten
Im folgenden Verfahren habe ich das Unique-Argument eingefügt, aber das CopyToRange-Argument weggelassen. Wenn Sie dieses Argument weglassen, werden Sie ENTWEDER muss ein Komma als Platzhalter für das Argument setzen
| 123456789 | Unter UniqueValuesFilter1()Dim rngDatabase As RangeDim rngCriteria As Range'Definieren der Datenbank- und KriterienbereicheSet rngDatabase = Sheets("Datenbank").Range("A1:H50")Set rngCriteria = Sheets("Criteria").Range("A1:H3")'Datenbank anhand der Kriterien filternrngDatabase.AdvancedFilter xlFilterInPlace, rngCriteria,,TrueEnd Sub |
ODER Sie müssen benannte Argumente verwenden, wie unten gezeigt.
| 123456789 | Unter UniqueValuesFilter2()Dim rngDatabase As RangeDim rngCriteria As Range'Definieren der Datenbank- und KriterienbereicheSet rngDatabase = Sheets("Datenbank").Range("A1:H50")Set rngCriteria = Sheets("Criteria").Range("A1:H3")'Datenbank anhand der Kriterien filternrngDatabase.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rngCriteria, Unique:=TrueEnd Sub |
Beide obigen Codebeispiele führen denselben Filter aus, wie unten gezeigt - die Daten mit nur eindeutigen Werten.
Verwenden des CopyTo-Arguments
| 123456789 | Unter CopyToFilter()Dim rngDatabase As RangeDim rngCriteria As Range'Definieren der Datenbank- und KriterienbereicheSet rngDatabase = Sheets("Datenbank").Range("A1:H50")Set rngCriteria = Sheets("Kriterien").Range("A1:H3")'kopieren Sie die gefilterten Daten an einen alternativen SpeicherortrngDatabase.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rngCriteria, CopyToRange:=Range("N1:U1"), Unique:=TrueEnd Sub |
Beachten Sie, dass wir die Namen der Argumente in der Codezeile des erweiterten Filters hätten weglassen können, aber die Verwendung benannter Argumente macht den Code leichter lesbar und verständlich.
Diese Zeile unten ist identisch mit der Zeile im oben gezeigten Verfahren.
| 1 | rngDatabase.AdvancedFilter xlFilterCopy, rngCriteria, Range("N1:U1"), True |
Nachdem der Code ausgeführt wurde, werden die Originaldaten weiterhin mit den gefilterten Daten am im Verfahren angegebenen Zielspeicherort angezeigt.
Duplikate aus den Daten entfernen
Wir können Duplikate aus den Daten entfernen, indem wir das Criteria-Argument weglassen und die Daten an einen neuen Speicherort kopieren.
| 1234567 | Sub RemoveDuplicates()Dim rngDatabase As Range'definiere die DatenbankSet rngDatabase = Sheets("Datenbank").Range("A1:H50")'Filtern Sie die Datenbank auf einen neuen Bereich, wobei Unique auf true gesetzt istrngDatabase.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("N1:U1"), Unique:=TrueEnd Sub |
