Excel VBA Autofilter: En komplet vejledning med eksempler

En masse Excel -funktioner er også tilgængelige til brug i VBA - og Autofilter metode er en sådan funktionalitet.

Hvis du har et datasæt, og du vil filtrere det ved hjælp af et kriterium, kan du nemt gøre det ved hjælp af indstillingen Filter i databåndet.

Og hvis du vil have en mere avanceret version af den, er der også et avanceret filter i Excel.

Så hvorfor endda bruge autofilteret i VBA?

Hvis du bare skal filtrere data og lave nogle grundlæggende ting, vil jeg anbefale at holde dig til den indbyggede filterfunktionalitet, som Excel -grænsefladen tilbyder.

Du bør bruge VBA Autofilter, når du vil filtrere dataene som en del af din automatisering (eller hvis det hjælper dig med at spare tid ved at gøre det hurtigere at filtrere dataene).

Antag f.eks., At du hurtigt vil filtrere data baseret på et rullemenu, og derefter kopiere disse filtrerede data til et nyt regneark.

Selvom dette kan gøres ved hjælp af den indbyggede filterfunktionalitet sammen med noget kopi-indsæt, kan det tage dig lang tid at gøre dette manuelt.

I et sådant scenario kan brug af VBA Autofilter fremskynde tingene og spare tid.

Bemærk: Jeg vil dække dette eksempel (om filtrering af data baseret på en rulleliste og kopiering til et nyt ark) senere i denne vejledning.

Excel VBA Autofilter Syntax

Udtryk. AutoFilter (_Field_, _Criteria1_, _Operator_, _Criteria2_, _VisibleDropDown_)

• Udtryk: Dette er det område, du vil anvende det automatiske filter på.
• Mark: [Valgfrit argument] Dette er det kolonnenummer, du vil filtrere. Dette tælles fra venstre i datasættet. Så hvis du vil filtrere data baseret på den anden kolonne, ville denne værdi være 2.
• Kriterier 1: [Valgfrit argument] Dette er de kriterier, som du vil filtrere datasættet på.
• Operatør: [Valgfrit argument] Hvis du også bruger kriterier 2, kan du kombinere disse to kriterier baseret på operatøren. Følgende operatører er tilgængelige til brug: xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlBottom10Percent, xlTop10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues
• Kriterier 2: [Valgfrit argument] Dette er det andet kriterium, som du kan filtrere datasættet på.
• VisibleDropDown: [Valgfrit argument] Du kan angive, om filterikonet skal vises i de filtrerede kolonner eller ej. Dette argument kan være SAND eller FALSK.

Bortset fra udtryk er alle de andre argumenter valgfri.

Hvis du ikke bruger noget argument, ville det blot anvende eller fjerne filterikonerne til kolonnerne.

Sub FilterRows () Regneark ("Filter Data"). Område ("A1"). AutoFilter End Sub

Ovenstående kode ville simpelthen anvende Autofilter -metoden på kolonnerne (eller hvis den allerede er anvendt, fjerner den den).

Dette betyder ganske enkelt, at hvis du ikke kan se filterikonerne i kolonneoverskrifterne, vil du begynde at se det, når ovenstående kode udføres, og hvis du kan se det, vil det blive fjernet.

Hvis du har filtrerede data, fjerner det filtrene og viser dig det fulde datasæt.

Lad os nu se nogle eksempler på brug af Excel VBA Autofilter, der vil gøre brugen overskuelig.

Eksempel: Filtrering af data baseret på en tekstbetingelse

Antag, at du har et datasæt som vist nedenfor, og du vil filtrere det baseret på kolonnen 'Item'.

Nedenstående kode filtrerer alle de rækker, hvor varen er 'Printer'.

Sub FilterRows () Worksheets ("Sheet1"). Range ("A1"). AutoFilter Field: = 2, Criteria1: = "Printer" End Sub

Ovenstående kode refererer til Sheet1 og inden for den refererer den til A1 (som er en celle i datasættet).

Bemærk, at vi her har brugt Field: = 2, da varekolonnen er den anden kolonne i vores datasæt fra venstre.

Hvis du nu tænker - hvorfor skal jeg gøre dette ved hjælp af en VBA -kode. Dette kan let gøres ved hjælp af indbygget filterfunktionalitet.

Du har ret!

Hvis det er alt, hvad du vil gøre, er det bedre at bruge den indbyggede filterfunktion.

Men når du læser den resterende vejledning, vil du se, at dette kan kombineres med en ekstra kode for at skabe kraftfuld automatisering.

Men før jeg viser dig dem, lad mig først dække et par eksempler for at vise dig, hvad al AutoFilter -metoden kan gøre.

Klik her for at downloade eksempelfilen og følge med.

Eksempel: Flere kriterier (AND/OR) i den samme kolonne

Antag, at jeg har det samme datasæt, og denne gang vil jeg filtrere alle de poster, hvor elementet enten er 'Printer' eller 'Projektor'.

Nedenstående kode ville gøre dette:

Sub FilterRowsOR () Regneark ("Sheet1"). Område ("A1"). AutoFilter -felt: = 2, Criteria1: = "Printer", Operator: = xlOr, Criteria2: = "Projector" End Sub

Bemærk, at jeg her har brugt xlOR operatør.

Dette fortæller VBA at bruge både kriterierne og filtrere dataene, hvis et af de to kriterier er opfyldt.

På samme måde kan du også bruge AND -kriterierne.

Hvis du f.eks. Vil filtrere alle de poster, hvor mængden er mere end 10 men mindre end 20, kan du bruge nedenstående kode:

Sub FilterRowsAND () Regneark ("Sheet1"). Område ("A1"). AutoFilter -felt: = 4, Criteria1: = "> 10", _ Operator: = xlAnd, Criteria2: = "<20" End Sub

Eksempel: Flere kriterier med forskellige kolonner

Antag, at du har følgende datasæt.

Med Autofilter kan du filtrere flere kolonner på samme tid.

Hvis du f.eks. Vil filtrere alle de poster, hvor varen er 'Printer', og salgsrepræsentanten er 'Mark', kan du bruge nedenstående kode:

Sub FilterRows () With Worksheets ("Sheet1"). Range ("A1"). AutoFilter field: = 2, Criteria1: = "Printer". AutoFilter field: = 3, Criteria1: = "Mark" End with End Sub

Eksempel: Filtrer de 10 bedste poster ved hjælp af autofiltermetoden

Antag, at du har nedenstående datasæt.

Nedenfor er koden, der giver dig de 10 bedste poster (baseret på mængdekolonnen):

Sub FilterRowsTop10 () ActiveSheet.Range ("A1"). AutoFilter Field: = 4, Criteria1: = "10", Operator: = xlTop10Items End Sub

I ovenstående kode har jeg brugt ActiveSheet. Du kan bruge arknavnet, hvis du vil.

Bemærk, at i dette eksempel, hvis du vil have de 5 bedste varer, skal du bare ændre tallet i Kriterier1: = ”10 ″ fra 10 til 5.

Så for top 5 varer ville koden være:

Sub FilterRowsTop5 () ActiveSheet.Range ("A1"). AutoFilter Field: = 4, Criteria1: = "5", Operator: = xlTop10Items End Sub

Det kan se underligt ud, men uanset hvor mange topelementer du vil have, forbliver operatørværdien altid xlTop10Produkter.

På samme måde ville nedenstående kode give dig de nederste 10 varer:

Sub FilterRowsBottom10 () ActiveSheet.Range ("A1"). AutoFilter Field: = 4, Criteria1: = "10", Operator: = xlBottom10Items End Sub

Og hvis du vil have de 5 nederste varer, skal du ændre tallet Kriterier1: = ”10 ″ fra 10 til 5.

Eksempel: Filtrer top 10 procent ved hjælp af autofiltermetoden

Antag, at du har det samme datasæt (som brugt i de foregående eksempler).

Nedenfor er koden, der giver dig de bedste 10 procent poster (baseret på mængdekolonnen):

Sub FilterRowsTop10 () ActiveSheet.Range ("A1"). AutoFilter Field: = 4, Criteria1: = "10", Operator: = xlTop10Percent End Sub

I vores datasæt, da vi har 20 poster, returnerer det de to øverste poster (hvilket er 10% af de samlede poster).

Eksempel: Brug af jokertegn i autofilter

Antag, at du har et datasæt som vist herunder:

Hvis du vil filtrere alle de rækker, hvor varenavnet indeholder ordet 'Board', kan du bruge nedenstående kode:

Sub FilterRowsWildcard () Regneark ("Sheet1"). Område ("A1"). AutoFilter Field: = 2, Criteria1: = "*Board*" End Sub

I ovenstående kode har jeg brugt jokertegnet * (stjerne) før og efter ordet ‘Board’ (som er kriterierne).

En stjerne kan repræsentere et vilkårligt antal tegn. Så dette ville filtrere ethvert element, der har ordet 'board' i det.

Eksempel: Kopier filtrerede rækker til et nyt ark

Hvis du ikke kun vil filtrere posterne baseret på kriterier, men også kopiere de filtrerede rækker, kan du bruge nedenstående makro.

Den kopierer de filtrerede rækker, tilføjer et nyt regneark og sætter derefter disse kopierede rækker ind i det nye ark.

Sub CopyFilteredRows () Dim rng As Range Dim ws As Worksheet If Worksheets ("Sheet1"). AutoFilterMode = False Then MsgBox "Der er ingen filtrerede rækker" Exit Sub End If Set rng = Worksheets ("Sheet1"). AutoFilter.Range Set ws = Worksheets.Add rng.Copy Range ("A1") End Sub

Ovenstående kode vil kontrollere, om der er filtrerede rækker i Sheet1 eller ej.

Hvis der ikke er filtrerede rækker, viser det en meddelelsesboks, der angiver det.

Og hvis der er filtrerede rækker, vil det kopiere dem, indsætte et nyt regneark og indsætte disse rækker på det nyindsatte regneark.

Eksempel: Filtrer data baseret på en celleværdi

Ved hjælp af Autofilter i VBA sammen med en rulleliste kan du oprette en funktionalitet, hvor alle poster for det pågældende element filtreres, så snart du vælger et element fra rullemenuen.

Noget som vist herunder:

Klik her for at downloade eksempelfilen og følge med.

Denne type konstruktion kan være nyttig, når du hurtigt vil filtrere data og derefter bruge dem videre i dit arbejde.

Nedenfor er koden, der gør dette:

Private Sub Worksheet_Change (ByVal Target As Range) If Target.Address = "$ B $ 2" Then If Range ("B2") = "All" Then Range ("A5"). AutoFilter Else Range ("A5"). AutoFilter Field : = 2, Criteria1: = Range ("B2") End If End If End Sub

Dette er en regnearkshændelseskode, som kun udføres, når der er en ændring i regnearket, og målcellen er B2 (hvor vi har rullemenuen).

Også en If Then Else -betingelse bruges til at kontrollere, om brugeren har valgt 'Alle' fra rullemenuen. Hvis alt er valgt, vises hele datasættet.

Denne kode er IKKE placeret i et modul.

I stedet skal det placeres i bagenden af ​​regnearket, der har disse data.

Her er trinene til at sætte denne kode i regnearkets kodevindue:

1. Åbn VB Editor (tastaturgenvej - ALT + F11).
2. I ruden Project Explorer skal du dobbeltklikke på det regnearksnavn, hvor du vil have denne filtreringsfunktion.
3. Kopier og indsæt ovenstående kode i regnearkets kodevindue.
4. Luk VB Editor.

Når du nu bruger rullelisten, filtrerer den automatisk dataene.

Dette er en regnearkhændelseskode, som kun udføres, når der er en ændring i regnearket, og målcellen er B2 (hvor vi har rullemenuen).

Også en If Then Else -betingelse bruges til at kontrollere, om brugeren har valgt 'Alle' fra rullemenuen. Hvis alt er valgt, vises hele datasættet.

Tænd/sluk Excel AutoFilter ved hjælp af VBA

Når du anvender Autofilter på en række celler, kan der allerede være nogle filtre på plads.

Du kan bruge nedenstående kode til at deaktivere alle forud anvendte autofiltre:

Sub TurnOFFAutoFilter () Regneark ("Sheet1"). AutoFilterMode = False End Sub

Denne kode kontrollerer hele arkene og fjerner alle anvendte filtre.

Hvis du ikke vil deaktivere filtre fra hele arket, men kun fra et specifikt datasæt, skal du bruge nedenstående kode:

Sub TurnOFFAutoFilter () If Worksheets ("Sheet1"). Range ("A1"). AutoFilter Then Worksheets ("Sheet1"). Range ("A1"). AutoFilter End If End Sub

Ovenstående kode kontrollerer, om der allerede er filtre på plads eller ej.

Hvis der allerede anvendes filtre, fjerner det det, ellers gør det ingenting.

På samme måde, hvis du vil tænde AutoFilter, skal du bruge nedenstående kode:

Sub TurnOnAutoFilter () Hvis ikke regneark ("Sheet1"). Område ("A4"). AutoFilter Herefter regneark ("Sheet1"). Range ("A4"). AutoFilter End If End Sub

Kontroller, om autofilter allerede er anvendt

Hvis du har et ark med flere datasæt, og du vil sikre dig, at du ved, at der ikke allerede er nogle filtre på plads, kan du bruge nedenstående kode.

Sub CheckforFilters () Hvis ActiveSheet.AutoFilterMode = True Så er MsgBox "Der er filtre allerede på plads" Else MsgBox "Der er ingen filtre" End If End Sub

Denne kode bruger en meddelelsesboksfunktion, der viser meddelelsen 'Der er filtre allerede på plads', når den finder filtre på arket, ellers viser den 'Der er ingen filtre'.

Vis alle data

Hvis du har filtre anvendt på datasættet, og du vil vise alle data, skal du bruge nedenstående kode:

Sub ShowAllData () If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End Sub

Ovenstående kode kontrollerer, om FilterMode er SAND eller FALSK.

Hvis det er sandt, betyder det, at et filter er blevet anvendt, og det bruger ShowAllData -metoden til at vise alle data.

Bemærk, at dette ikke fjerner filtrene. Filterikonerne er stadig tilgængelige til brug.

Brug af AutoFilter på beskyttede ark

Som standard fungerer filtrene ikke, når du beskytter et ark.

Hvis du allerede har filtre på plads, kan du aktivere AutoFilter for at sikre, at det fungerer selv på beskyttede ark.

For at gøre dette skal du markere indstillingen Brug autofilter, mens du beskytter arket.

Selvom dette virker, når du allerede har filtre på plads, fungerer det ikke, hvis du prøver at tilføje autofiltere ved hjælp af en VBA -kode.

Da arket er beskyttet, ville det ikke tillade nogen makro at køre og foretage ændringer i autofilteret.

Så du skal bruge en kode til at beskytte regnearket og sikre, at autofiltre er aktiveret i det.

Dette kan være nyttigt, når du har oprettet et dynamisk filter (noget jeg dækkede i eksemplet - 'Filtrer data baseret på en celleværdi').

Nedenfor er koden, der beskytter arket, men på samme tid giver dig mulighed for at bruge filtre samt VBA -makroer i det.

Private Sub Workbook_Open () With Worksheets ("Sheet1") .EnableAutoFilter = True .Protect Password: = "password", Contents: = True, UserInterfaceOnly: = True End With End Sub

Denne kode skal placeres i ThisWorkbook -kodevinduet.

Her er trinene til at sætte koden i ThisWorkbook -kodevinduet:

1. Åbn VB Editor (tastaturgenvej - ALT + F11).
2. I ruden Project Explorer skal du dobbeltklikke på ThisWorkbook-objektet.
3. Kopier og indsæt ovenstående kode i kodevinduet, der åbnes.

Så snart du åbner projektmappen og aktiverer makroer, kører den makroen automatisk og beskytter Sheet1.

Før det gøres, vil det dog angive 'EnableAutoFilter = True', hvilket betyder, at filtrene også ville fungere i det beskyttede ark.

Det sætter også argumentet 'UserInterfaceOnly' til 'True'. Det betyder, at mens regnearket er beskyttet, fortsætter VBA -makrokoden med at fungere.

Du kan også lide følgende VBA -øvelser:

• Excel VBA -sløjfer.
• Filtrer celler med fed skriftformatering.
• Optagelse af en makro.
• Sorter data ved hjælp af VBA.
• Sorter regnearkfaner i Excel.