Excel VBA Autofilter: En komplet guide med eksempler

En masse Excel-funktioner kan også bruges i VBA – og Autofilter-metoden er en af disse funktioner.

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 filterindstillingen i fanebåndet Data.

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

Så hvorfor overhovedet bruge Autofilter i VBA?

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

Du bør bruge VBA Autofilter, når du ønsker at filtrere data 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).

For eksempel kan du antage, at du hurtigt vil filtrere dataene baseret på et drop-down-valg og derefter kopiere disse filtrerede data til et nyt regneark.

Selv om dette kan gøres ved hjælp af den indbyggede filterfunktionalitet sammen med noget copy-paste, kan det tage dig meget tid at gøre dette manuelt.

I et sådant scenarie kan du ved hjælp af VBA Autofilter fremskynde tingene og spare tid.

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

Excel VBA Autofilter Syntaks

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

• Udtryk: Dette er det område, som du vil anvende det automatiske filter.
• Felt:
• Felt: Dette er det område, som du vil anvende det automatiske filter på: Dette er det kolonnenummer, som du ønsker at filtrere. Dette tælles fra venstre i datasættet. Så hvis du ønsker at filtrere data baseret på den anden kolonne, vil denne værdi være 2.
• Kriterium1: Dette er det kriterium, som du ønsker at filtrere datasættet på.
• Operatør: Dette er det kriterium, som du ønsker at filtrere datasættet på: Hvis du også bruger kriterium 2, kan du kombinere disse to kriterier baseret på operatøren. Følgende operatorer kan bruges: xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlBottom10Percent, xlTop10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues
• Kriterium2: Dette er det andet kriterium, som du kan filtrere datasættet ud fra.
• VisibleDropDown: Du kan angive, om du vil have filterets drop-down-ikon til at blive vist i de filtrerede kolonner eller ej. Dette argument kan være TRUE eller FALSE.

Afhængig af Expression er alle de andre argumenter valgfrie.

Hvis du ikke bruger noget argument, vil det blot anvende eller fjerne filterikonerne på kolonnerne.

Sub FilterRows()Worksheets("Filter Data").Range("A1").AutoFilterEnd Sub

Overstående kode vil blot anvende Autofilter-metoden på kolonnerne (eller hvis den allerede er anvendt, vil den fjerne den).

Det betyder simpelthen, at hvis du ikke kan se filterikonerne i kolonneoverskrifterne, vil du begynde at se dem, når ovenstående kode udføres, og hvis du kan se dem, vil de blive fjernet.

Hvis du har nogen filtrerede data, vil den fjerne filtrene og vise dig det fulde datasæt.

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

Eksempel:

Sæt, du har et datasæt som vist nedenfor, og du vil filtrere det baseret på kolonnen ‘Item’.

Den nedenstående kode vil filtrere alle de rækker, hvor elementet er ‘Printer’.

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

Overstående kode henviser til Sheet1, og i den henviser den til A1 (som er en celle i datasættet).

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

Men før jeg viser dig disse, vil jeg først gennemgå et par eksempler for at vise dig, hvad alt AutoFilter-metoden kan gøre.

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

Eksempel: Antag, at jeg har det samme datasæt, og denne gang vil jeg filtrere alle de poster, hvor elementet enten er “Printer” eller “Projector”.

Med nedenstående kode kan dette gøres:

Sub FilterRowsOR()Worksheets("Sheet1").Range("A1").AutoFilter Field:=2, Criteria1:="Printer", Operator:=xlOr, Criteria2:="Projector"End Sub

Bemærk, at jeg her har brugt xlOR-operatoren.

Dette fortæller VBA, at det skal bruge begge kriterier og filtrere dataene, hvis et af de to kriterier er opfyldt.

Sådan kan du også bruge AND-kriterierne.

For eksempel, hvis du vil filtrere alle de poster, hvor mængden er mere end 10, men mindre end 20, kan du bruge nedenstående kode:

Sub FilterRowsAND()Worksheets("Sheet1").Range("A1").AutoFilter Field:=4, Criteria1:=">10", _ Operator:=xlAnd, Criteria2:="<20"End Sub

Eksempel:

Sub FilterRowsAND()Worksheets("Sheet1").Range("A1").AutoFilter Field:=4, Criteria1:=">10", _ Operator:=xlAnd, Criteria2:="<20"End Sub

Sæt, 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 sælgeren 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 WithEnd Sub

Eksempel:

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

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

Nedenfor er den kode, der giver dig de 10 øverste poster (baseret på kolonnen mængde):

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

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

Bemærk, at hvis du i dette eksempel vil have de øverste 5 poster, skal du blot ændre tallet i Criteria1:=”10″ fra 10 til 5.

Så for de øverste 5 poster ville koden være:

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

Det ser måske underligt ud, men uanset hvor mange øverste poster du vil have, forbliver Operator-værdien altid xlTop10Items.

Sådan ville nedenstående kode give dig de nederste 10 elementer:

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

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

Eksempel:

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

Nedenfor er koden, der giver dig de øverste 10 procent poster (baseret på kvantitetskolonnen):

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

I vores datasæt, da vi har 20 poster, vil den returnere de øverste 2 poster (hvilket er 10 % af de samlede poster).

Eksempel: Hvis du bruger den samme metode til at filtrere de øverste 10 procent ved hjælp af AutoFilter-metoden

, vil den returnere de øverste 2 poster (hvilket er 10 % af de samlede poster).

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

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

Sub FilterRowsWildcard()Worksheets("Sheet1").Range("A1").AutoFilter Field:=2, Criteria1:="*Board*"End Sub

I ovenstående kode har jeg brugt wildcard-tegnet * (stjerne) før og efter ordet “Board” (som er kriteriet).

En asterisk kan repræsentere et vilkårligt antal tegn. Så dette ville filtrere alle elementer, der indeholder ordet “board”.

Eksempel: Hvis du ikke blot vil filtrere posterne ud fra kriterierne, 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 indsætter derefter disse kopierede rækker i det nye ark.

Sub CopyFilteredRows()Dim rng As RangeDim ws As WorksheetIf Worksheets("Sheet1").AutoFilterMode = False ThenMsgBox "There are no filtered rows"Exit SubEnd IfSet rng = Worksheets("Sheet1").AutoFilter.RangeSet ws = Worksheets.Addrng.Copy Range("A1")End Sub

Overstående kode vil kontrollere, om der er nogen filtrerede rækker i ark1 eller ej.

Hvis der ikke er nogen filtrerede rækker, vises en meddelelsesboks, der angiver dette.

Og hvis der er filtrerede rækker, kopieres disse, der indsættes et nyt regneark, og disse rækker indsættes i det nyligt indsatte regneark.

Eksempel:

Med Autofilter i VBA sammen med en drop-down-liste kan du oprette en funktionalitet, hvor alle poster for dette emne filtreres, så snart du vælger et emne i drop-down-listen.

Som vist nedenfor:

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 den kode, der gør dette:

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

Dette er en regnearksbegivenhedskode, som kun bliver udført, når der sker en ændring i regnearket, og målcellen er B2 (hvor vi har drop-down’en).

Der bruges også en If Then Else-betingelse til at kontrollere, om brugeren har valgt “Alle” i drop-down’en. Hvis Alt er valgt, vises hele datasættet.

Denne kode placeres IKKE i et modul.

Den skal i stedet placeres i backend af det regneark, der har disse data.

Her er trinene til at placere denne kode i regnearkets kodevindue:

1. Åbn VB-editoren (tastaturgenvej – ALT + F11).
2. Dobbeltklik på navnet på det regneark, hvor du ønsker denne filtreringsfunktionalitet, i ruden Projekt Explorer.
3. Kopier og indsæt ovenstående kode i kodevinduet for regnearket.
4. Slut VB Editor.

Nu vil den automatisk filtrere dataene, når du bruger rullelisten.

Dette er en regnearksbegivenhedskode, som kun bliver udført, når der sker en ændring i regnearket, og målcellen er B2 (hvor vi har drop-down-listen).

Der bruges også en If Then Else-betingelse til at kontrollere, om brugeren har valgt “Alle” i drop-down-listen. Hvis Alt er valgt, vises hele datasættet.

Slå Excel AutoFilter til/fra ved hjælp af VBA

Når du anvender Autofilter på et område af celler, kan der allerede være nogle filtre på plads.

Du kan bruge nedenstående kode til at slå eventuelle forudanvendte autofiltre fra:

Sub TurnOFFAutoFilter() Worksheets("Sheet1").AutoFilterMode = FalseEnd Sub

Denne kode kontrollerer hele arket og fjerner eventuelle filtre, der er blevet anvendt.

Hvis du ikke ønsker at slå filtre fra hele arket fra, men kun fra et bestemt datasæt, skal du bruge nedenstående kode:

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

Overstående kode kontrollerer, om der allerede er anvendt filtre eller ej.

Hvis der allerede er anvendt filtre, fjerner den dem, ellers gør den ingenting.

Sådan skal du bruge nedenstående kode, hvis du vil slå AutoFilter til:

Sub TurnOnAutoFilter() If Not Worksheets("Sheet1").Range("A4").AutoFilter Then Worksheets("Sheet1").Range("A4").AutoFilter End IfEnd 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 filtre på plads, kan du bruge nedenstående kode.

Sub CheckforFilters()If ActiveSheet.AutoFilterMode = True ThenMsgBox "There are Filters already in place"ElseMsgBox "There are no filters"End IfEnd Sub

Denne kode bruger en beskedboksfunktion, der viser meddelelsen “Der er allerede anvendt filtre”, hvis den finder filtre på arket, ellers vises “Der er ingen filtre”.

Vis alle data

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

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

Overstående kode kontrollerer, om FilterMode er TRUE eller FALSE.

Hvis det er sandt, betyder det, at der er anvendt et filter, og den bruger ShowAllData-metoden til at vise alle dataene.

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.

Mens dette virker, når du allerede har filtre på plads, vil det ikke virke, hvis du forsøger at tilføje Autofilter ved hjælp af en VBA-kode.

Da arket er beskyttet, vil det ikke tillade, at en makro kan køre og foretage ændringer i Autofilter.

Så du skal bruge en kode til at beskytte regnearket og sørge for, 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 den kode, der beskytter arket, men som samtidig 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:=TrueEnd WithEnd Sub

Denne kode skal placeres i kodevinduet ThisWorkbook.

Her er trinene til at placere koden i ThisWorkbook-kodevinduet:

1. Åbn VB-editoren (tastaturgenvej – ALT + F11).
2. Dobbeltklik på ThisWorkbook-objektet i vinduet Project Explorer.
3. I det kodevindue, der åbnes, kopierer du ovenstående kode og indsætter den.

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

Hvor den gør det, vil den imidlertid angive ‘EnableAutoFilter = True’, hvilket betyder, at filtrene også vil fungere i det beskyttede ark.

Der indstiller også argumentet ‘UserInterfaceOnly’ til ‘True’. Det betyder, at mens regnearket er beskyttet, vil VBA-makrokoden fortsat fungere.

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

• Excel VBA Loops.
• Filtrer celler med fed skrifttypeformatering.
• Optagelse af en makro.
• Sortere data ved hjælp af VBA.
• Sortere faner i regneark i Excel.