Mange Excel -funksjoner er også tilgjengelige for bruk i VBA - og Autofilter metode er en slik funksjonalitet.
Hvis du har et datasett og du vil filtrere det ved hjelp av et kriterium, kan du enkelt gjøre det ved å bruke alternativet Filter i databåndet.
Og hvis du vil ha en mer avansert versjon av den, er det også et avansert filter i Excel.
Så hvorfor bruke autofilteret i VBA?
Hvis du bare trenger å filtrere data og gjøre noen grunnleggende ting, vil jeg anbefale å holde deg til den innebygde filterfunksjonaliteten som Excel -grensesnittet tilbyr.
Du bør bruke VBA Autofilter når du vil filtrere dataene som en del av automatiseringen din (eller hvis det hjelper deg å spare tid ved å gjøre det raskere å filtrere dataene).
Anta for eksempel at du raskt vil filtrere dataene basert på et rullegardinvalg, og deretter kopiere disse filtrerte dataene til et nytt regneark.
Selv om dette kan gjøres ved hjelp av den innebygde filterfunksjonaliteten sammen med litt kopi-lim, kan det ta deg lang tid å gjøre dette manuelt.
I et slikt scenario kan bruk av VBA Autofilter fremskynde ting og spare tid.
Merk: Jeg vil dekke dette eksempelet (om filtrering av data basert på et rullegardinvalg og kopiering til et nytt ark) senere i denne opplæringen.
Excel VBA Autofilter Syntax
Uttrykk. AutoFilter (_Field_, _Criteria1_, _Operator_, _Criteria2_, _VisibleDropDown_)
- Uttrykk: Dette er området du vil bruke autofilteret på.
- Felt: [Valgfritt argument] Dette er kolonnenummeret du vil filtrere. Dette telles fra venstre i datasettet. Så hvis du vil filtrere data basert på den andre kolonnen, vil denne verdien være 2.
- Kriterier 1: [Valgfritt argument] Dette er kriteriene du vil filtrere datasettet ut fra.
- Operatør: [Valgfritt argument] Hvis du også bruker kriterier 2, kan du kombinere disse to kriteriene basert på operatøren. Følgende operatører er tilgjengelige for bruk: xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlBottom10Percent, xlTop10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues
- Kriterier 2: [Valgfritt argument] Dette er det andre kriteriet du kan filtrere datasettet på.
- VisibleDropDown: [Valgfritt argument] Du kan angi om du vil at rullegardinikonet for filter skal vises i de filtrerte kolonnene eller ikke. Dette argumentet kan være SANN eller FALSK.
Bortsett fra uttrykk, er alle de andre argumentene valgfrie.
Hvis du ikke bruker noe argument, ville det ganske enkelt bruke eller fjerne filterikonene til kolonnene.
Sub FilterRows () Regneark ("Filter Data"). Område ("A1"). AutoFilter End Sub
Koden ovenfor vil ganske enkelt bruke Autofilter -metoden på kolonnene (eller hvis den allerede er brukt, vil den fjerne den).
Dette betyr ganske enkelt at hvis du ikke kan se filterikonene i kolonneoverskriftene, vil du begynne å se den når denne koden ovenfor er utført, og hvis du kan se den, blir den fjernet.
Hvis du har filtrerte data, vil det fjerne filtrene og vise deg hele datasettet.
La oss nå se noen eksempler på bruk av Excel VBA Autofilter som vil gjøre bruken tydelig.
Eksempel: Filtrering av data basert på en tekstbetingelse
Anta at du har et datasett som vist nedenfor, og du vil filtrere det basert på "Element" -kolonnen.
Koden nedenfor filtrerer alle radene der elementet er ‘Skriver’.
Sub FilterRows () Worksheets ("Sheet1"). Range ("A1"). AutoFilter Field: = 2, Criteria1: = "Printer" End Sub
Koden ovenfor refererer til Sheet1 og i den refererer den til A1 (som er en celle i datasettet).
Vær oppmerksom på at her har vi brukt Field: = 2, ettersom elementkolonnen er den andre kolonnen i datasettet vårt til venstre.
Nå hvis du tenker - hvorfor trenger jeg å gjøre dette ved hjelp av en VBA -kode. Dette kan enkelt gjøres ved hjelp av innebygd filterfunksjonalitet.
Du har rett!
Hvis dette er alt du vil gjøre, bedre å bruke den innebygde filterfunksjonaliteten.
Men mens du leser den gjenværende opplæringen, ser du at dette kan kombineres med litt ekstra kode for å skape kraftig automatisering.
Men før jeg viser deg dem, la meg først dekke noen eksempler for å vise deg hva all AutoFilter -metoden kan gjøre.
Klikk her for å laste ned eksempelfilen og følge med.
Eksempel: Flere kriterier (AND/OR) i samme kolonne
Anta at jeg har det samme datasettet, og denne gangen vil jeg filtrere alle postene der elementet enten er ‘Printer’ eller ‘Projector’.
Koden nedenfor vil gjøre dette:
Sub FilterRowsOR () Regneark ("Sheet1"). Område ("A1"). AutoFilter -felt: = 2, Criteria1: = "Printer", Operator: = xlOr, Criteria2: = "Projector" End Sub
Merk at her har jeg brukt xlOR operatør.
Dette forteller VBA å bruke både kriteriene og filtrere dataene hvis noen av de to kriteriene er oppfylt.
På samme måte kan du også bruke AND -kriteriene.
For eksempel, hvis du vil filtrere alle postene der mengden er mer enn 10 men mindre enn 20, kan du bruke koden nedenfor:
Sub FilterRowsAND () Regneark ("Sheet1"). Område ("A1"). AutoFilter -felt: = 4, Criteria1: = "> 10", _ Operator: = xlAnd, Criteria2: = "<20" End Sub
Eksempel: Flere kriterier med forskjellige kolonner
Anta at du har følgende datasett.
Med Autofilter kan du filtrere flere kolonner samtidig.
For eksempel, hvis du vil filtrere alle postene der varen er "Skriver" og salgsrepresentanten er "Merk", kan du bruke koden nedenfor:
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 beste postene ved hjelp av autofiltermetoden
Anta at du har datasettet nedenfor.
Nedenfor er koden som gir deg de 10 beste postene (basert på mengdekolonnen):
Sub FilterRowsTop10 () ActiveSheet.Range ("A1"). AutoFilter Field: = 4, Criteria1: = "10", Operator: = xlTop10Items End Sub
I koden ovenfor har jeg brukt ActiveSheet. Du kan bruke arknavnet hvis du vil.
Vær oppmerksom på at i dette eksemplet, hvis du vil få de fem beste elementene, må du bare endre tallet Kriterier 1: = ”10 ″ fra 10 til 5.
Så for topp 5 -elementene vil koden være:
Sub FilterRowsTop5 () ActiveSheet.Range ("A1"). AutoFilter Field: = 4, Criteria1: = "5", Operator: = xlTop10Items End Sub
Det kan se rart ut, men uansett hvor mange toppartikler du vil ha, forblir operatørverdien alltid xlTop10Items.
På samme måte vil koden nedenfor gi deg de 10 nederste elementene:
Sub FilterRowsBottom10 () ActiveSheet.Range ("A1"). AutoFilter Field: = 4, Criteria1: = "10", Operator: = xlBottom10Items End Sub
Og hvis du vil ha de fem nederste elementene, endre tallet Kriterier 1: = ”10 ″ fra 10 til 5.
Eksempel: Filtrer topp 10 prosent ved hjelp av autofiltermetoden
Anta at du har det samme datasettet (som brukt i de foregående eksemplene).
Nedenfor er koden som vil gi deg de 10 beste postene (basert på mengdekolonnen):
Sub FilterRowsTop10 () ActiveSheet.Range ("A1"). AutoFilter Field: = 4, Criteria1: = "10", Operator: = xlTop10Percent End Sub
I datasettet vårt, siden vi har 20 poster, vil det returnere de to beste postene (som er 10% av de totale postene).
Eksempel: Bruke jokertegn i autofilter
Anta at du har et datasett som vist nedenfor:
Hvis du vil filtrere alle radene der varenavnet inneholder ordet ‘Board’, kan du bruke koden nedenfor:
Sub FilterRowsWildcard () Regneark ("Sheet1"). Område ("A1"). AutoFilter Field: = 2, Criteria1: = "*Board*" End Sub
I koden ovenfor har jeg brukt jokertegnet * (stjerne) før og etter ordet ‘Board’ (som er kriteriene).
En stjerne kan representere et hvilket som helst antall tegn. Så dette vil filtrere ethvert element som har ordet "bord" i det.
Eksempel: Kopier filtrerte rader til et nytt ark
Hvis du ikke bare vil filtrere postene basert på kriterier, men også kopiere de filtrerte radene, kan du bruke makroen nedenfor.
Den kopierer de filtrerte radene, legger til et nytt regneark og limer deretter de kopierte radene inn i det nye arket.
Sub CopyFilteredRows () Dim rng As Range Dim ws As Worksheet If Worksheets ("Sheet1"). AutoFilterMode = False Then MsgBox "Det er ingen filtrerte rader" Exit Sub End If Set rng = Worksheets ("Sheet1"). AutoFilter.Range Set ws = Arbeidsark.Legg til rng.Copy Range ("A1") End Sub
Koden ovenfor vil kontrollere om det er noen filtrerte rader i Sheet1 eller ikke.
Hvis det ikke er noen filtrerte rader, vil det vise en meldingsboks som sier det.
Og hvis det er filtrerte rader, vil det kopiere disse, sette inn et nytt regneark og lime inn disse radene på det nylig innsatte regnearket.
Eksempel: Filtrer data basert på en celleverdi
Ved å bruke Autofilter i VBA sammen med en rullegardinliste kan du opprette en funksjonalitet der så snart du velger et element fra rullegardinlisten, blir alle postene for det elementet filtrert.
Noe som vist nedenfor:
Klikk her for å laste ned eksempelfilen og følge med.
Denne konstruksjonstypen kan være nyttig når du raskt vil filtrere data og deretter bruke den videre i arbeidet ditt.
Nedenfor er koden som gjø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 hendelseskode for regnearket, som bare blir utført når det er en endring i regnearket og målcellen er B2 (der vi har rullegardinmenyen).
I tillegg brukes en If Then Else -betingelse for å sjekke om brukeren har valgt ‘Alle’ fra rullegardinmenyen. Hvis alt er valgt, vises hele datasettet.
Denne koden er IKKE plassert i en modul.
I stedet må den plasseres i bakenden av regnearket som har disse dataene.
Her er trinnene for å sette denne koden i regnearkets kodevindu:
- Åpne VB Editor (hurtigtast - ALT + F11).
- I Project Explorer-ruten dobbeltklikker du på regnearknavnet der du vil ha denne filtreringsfunksjonaliteten.
- Kopier og lim inn koden ovenfor i regnearkets kodevindu.
- Lukk VB Editor.
Når du bruker rullegardinlisten, filtrerer den automatisk dataene.
Dette er en hendelseskode for regnearket, som bare blir utført når det er en endring i regnearket og målcellen er B2 (der vi har rullegardinmenyen).
I tillegg brukes en If Then Else -betingelse for å sjekke om brukeren har valgt ‘Alle’ fra rullegardinmenyen. Hvis alt er valgt, vises hele datasettet.
Slå Excel AutoFilter PÅ/AV med VBA
Når du bruker Autofilter på en rekke celler, kan det allerede være noen filtre på plass.
Du kan bruke koden nedenfor og slå av alle forhåndsapplikerte autofiltre:
Sub TurnOFFAutoFilter () Regneark ("Sheet1"). AutoFilterMode = False End Sub
Denne koden sjekker hele arkene og fjerner alle filtre som er brukt.
Hvis du ikke vil slå av filtre fra hele arket, men bare fra et bestemt datasett, bruker du koden nedenfor:
Sub TurnOFFAutoFilter () If Worksheets ("Sheet1"). Range ("A1"). AutoFilter Then Worksheets ("Sheet1"). Range ("A1"). AutoFilter End If End Sub
Koden ovenfor kontrollerer om det allerede er filtre på plass eller ikke.
Hvis filtre allerede er påført, fjerner det det, ellers gjør det ingenting.
På samme måte, hvis du vil slå på AutoFilter, bruker du koden nedenfor:
Sub TurnOnAutoFilter () If Not Worksheets ("Sheet1"). Range ("A4"). AutoFilter Then Worksheets ("Sheet1"). Range ("A4"). AutoFilter End If End Sub
Sjekk om autofilter allerede er brukt
Hvis du har et ark med flere datasett, og du vil være sikker på at du vet at det ikke er noen filtre på plass, kan du bruke koden nedenfor.
Sub CheckforFilters () Hvis ActiveSheet.AutoFilterMode = True Da er MsgBox "Det er filtre på plass" Else MsgBox "Det er ingen filtre" End If End Sub
Denne koden bruker en meldingsboksfunksjon som viser meldingen "Det er filtre allerede på plass" når den finner filtre på arket, ellers viser den "Det er ingen filtre".
Vis alle data
Hvis du har filtre brukt på datasettet og du vil vise alle dataene, bruker du koden nedenfor:
Sub ShowAllData () If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End Sub
Koden ovenfor kontrollerer om FilterMode er TRUE eller FALSE.
Hvis det er sant, betyr det at et filter er brukt, og det bruker ShowAllData -metoden for å vise alle dataene.
Vær oppmerksom på at dette ikke fjerner filtrene. Filterikonene er fremdeles tilgjengelige for bruk.
Bruke autofilter på beskyttede ark
Som standard, når du beskytter et ark, fungerer ikke filtrene.
Hvis du allerede har på plass filtre, kan du aktivere AutoFilter for å sikre at det fungerer selv på beskyttede ark.
For å gjøre dette, sjekk alternativet Bruk autofilter mens du beskytter arket.
Selv om dette fungerer når du allerede har filtre på plass, vil det ikke fungere hvis du prøver å legge til autofilter ved hjelp av en VBA -kode.
Siden arket er beskyttet, vil det ikke tillate noen makro å kjøre og gjøre endringer i autofilteret.
Så du må bruke en kode for å beskytte regnearket og sørge for at autofiltre er aktivert i det.
Dette kan være nyttig når du har opprettet et dynamisk filter (noe jeg dekket i eksemplet - 'Filtrer data basert på en celleverdi').
Nedenfor er koden som skal beskytte arket, men som samtidig lar deg bruke filtre så vel som 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 koden må plasseres i ThisWorkbook -kodevinduet.
Her er trinnene for å sette koden i ThisWorkbook -kodevinduet:
- Åpne VB Editor (hurtigtast - ALT + F11).
- Dobbeltklikk på ThisWorkbook-objektet i Project Explorer-ruten.
- Kopier og lim inn koden ovenfor i kodevinduet som åpnes.
Så snart du åpner arbeidsboken og aktiverer makroer, kjører den makroen automatisk og beskytter ark1.
Imidlertid, før du gjør det, vil den spesifisere 'EnableAutoFilter = True', noe som betyr at filtrene også ville fungere i det beskyttede arket.
Den setter også argumentet 'UserInterfaceOnly' til 'True'. Dette betyr at mens regnearket er beskyttet, vil VBA -makrokoden fortsette å fungere.
Du kan også like følgende VBA -opplæringsprogrammer:
- Excel VBA -sløyfer.
- Filtrer celler med fet skriftformatering.
- Ta opp en makro.
- Sorter data ved hjelp av VBA.
- Sorter regnearkfaner i Excel.
