Excel VBA Autofilter: En komplett guide med exempel

Många Excel -funktioner är också tillgängliga för användning i VBA - och Autofilter metod är en sådan funktion.

Om du har en datauppsättning och du vill filtrera den med hjälp av ett kriterium kan du enkelt göra det med hjälp av alternativet Filter i databandet.

Och om du vill ha en mer avancerad version av det finns det ett avancerat filter också i Excel.

Varför ska du då använda autofiltret i VBA?

Om du bara behöver filtrera data och göra några grundläggande saker rekommenderar jag att du håller dig till den inbyggda filterfunktionen som Excel -gränssnittet erbjuder.

Du bör använda VBA Autofilter när du vill filtrera data som en del av din automatisering (eller om det hjälper dig att spara tid genom att göra det snabbare att filtrera data).

Anta till exempel att du snabbt vill filtrera data baserat på ett rullgardinsmeny och sedan kopiera dessa filtrerade data till ett nytt kalkylblad.

Även om detta kan göras med den inbyggda filterfunktionen tillsammans med lite kopierings-klistra in, kan det ta mycket tid att göra detta manuellt.

I ett sådant scenario kan användning av VBA Autofilter påskynda saker och spara tid.

Notera: Jag kommer att täcka detta exempel (om filtrering av data baserat på ett nedrullningsbart urval och kopiering till ett nytt blad) senare i den här självstudien.

Excel VBA Autofilter Syntax

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

• Uttryck: Detta är intervallet som du vill använda autofiltret på.
• Fält: [Valfritt argument] Detta är kolumnnumret som du vill filtrera. Detta räknas från vänster i datamängden. Så om du vill filtrera data baserat på den andra kolumnen, skulle detta värde vara 2.
• Kriterier 1: [Valfritt argument] Detta är de kriterier som du vill filtrera datamängden utifrån.
• Operatör: [Valfritt argument] Om du också använder kriterier 2 kan du kombinera dessa två kriterier baserat på operatören. Följande operatörer är tillgängliga för användning: xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlBottom10Percent, xlTop10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues
• Kriterier2: [Valfritt argument] Detta är det andra kriteriet på vilket du kan filtrera datauppsättningen.
• VisibleDropDown: [Valfritt argument] Du kan ange om du vill att filtermenyn ska visas i de filtrerade kolumnerna eller inte. Detta argument kan vara SANT eller FALSKT.

Förutom uttryck är alla andra argument valfria.

Om du inte använder något argument skulle det helt enkelt tillämpa eller ta bort filterikonerna i kolumnerna.

Sub FilterRows () Worksheets ("Filter Data"). Range ("A1"). AutoFilter End Sub

Ovanstående kod skulle helt enkelt tillämpa Autofilter -metoden på kolumnerna (eller om den redan tillämpas kommer den att ta bort den).

Detta betyder helt enkelt att om du inte kan se filterikonerna i kolumnrubrikerna kommer du att börja se den när ovanstående kod körs, och om du kan se den kommer den att tas bort.

Om du har några filtrerade data kommer det att ta bort filtren och visa dig hela datauppsättningen.

Låt oss nu se några exempel på hur du använder Excel VBA Autofilter som gör användningen tydlig.

Exempel: Filtrering av data baserat på ett textvillkor

Anta att du har en dataset som visas nedan och du vill filtrera den baserat på kolumnen "Artikel".

Koden nedan filtrerar alla rader där objektet är "Skrivare".

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

Ovanstående kod hänvisar till Sheet1 och inom den hänvisar den till A1 (som är en cell i datamängden).

Observera att här har vi använt Field: = 2, eftersom objektkolumnen är den andra kolumnen i vår dataset från vänster.

Nu om du tänker - varför behöver jag göra det här med en VBA -kod. Detta kan enkelt göras med hjälp av inbyggd filterfunktion.

Du har rätt!

Om detta är allt du vill göra är det bättre att använda den inbyggda filterfunktionen.

Men när du läser den återstående handledningen ser du att detta kan kombineras med lite extra kod för att skapa kraftfull automatisering.

Men innan jag visar dig dem, låt mig först täcka några exempel för att visa dig vad all AutoFilter -metod kan göra.

Klicka här för att ladda ner exempelfilen och följa med.

Exempel: Flera kriterier (OCH/ELLER) i samma kolumn

Antag att jag har samma datauppsättning, och den här gången vill jag filtrera alla poster där objektet antingen är "Skrivare" eller "Projektor".

Koden nedan skulle göra detta:

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

Observera att här har jag använt xlOR operatör.

Detta uppmanar VBA att använda både kriterierna och filtrera data om något av de två kriterierna är uppfyllda.

På samma sätt kan du också använda OCH -kriterierna.

Om du till exempel vill filtrera alla poster där mängden är mer än 10 men mindre än 20 kan du använda koden nedan:

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

Exempel: Flera kriterier med olika kolumner

Anta att du har följande dataset.

Med Autofilter kan du filtrera flera kolumner samtidigt.

Om du till exempel vill filtrera alla poster där varan är "Skrivare" och säljaren är "Märke", kan du använda koden nedan:

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

Exempel: Filtrera de 10 bästa posterna med hjälp av autofiltermetoden

Anta att du har nedanstående dataset.

Nedan är koden som ger dig de 10 bästa posterna (baserat på kvantitetskolumnen):

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

I ovanstående kod har jag använt ActiveSheet. Du kan använda bladnamnet om du vill.

Observera att i det här exemplet, om du vill få de fem bästa artiklarna, ändrar du bara numret Kriterier1: = ”10 ″ från 10 till 5.

Så för topp 5 -artiklarna skulle koden vara:

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

Det kan se konstigt ut, men oavsett hur många toppartiklar du vill ha kvar operatörsvärdet xlTop10Produkter.

På samma sätt skulle nedanstående kod ge dig de 10 nedersta objekten:

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

Och om du vill ha de fem nedre objekten ändrar du numret Kriterier1: = ”10 ″ från 10 till 5.

Exempel: Filtrera de 10 bästa procenten med hjälp av autofiltermetoden

Anta att du har samma datauppsättning (som används i föregående exempel).

Nedan är koden som ger dig de 10 bästa procentsposterna (baserat på kvantitetskolumnen):

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

I vår datauppsättning, eftersom vi har 20 poster, returnerar den de två bästa posterna (vilket är 10% av de totala posterna).

Exempel: Använda jokertecken i autofilter

Anta att du har en dataset som visas nedan:

Om du vill filtrera alla rader där artikelnamnet innehåller ordet "Board" kan du använda koden nedan:

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

I koden ovan har jag använt jokertecknet * (asterisk) före och efter ordet ‘Board’ (vilket är kriteriet).

En asterisk kan representera valfritt antal tecken. Så detta skulle filtrera alla objekt som har ordet "tavla" i det.

Exempel: Kopiera filtrerade rader till ett nytt ark

Om du inte bara vill filtrera posterna utifrån kriterier utan också kopiera de filtrerade raderna kan du använda makrot nedan.

Den kopierar de filtrerade raderna, lägger till ett nytt kalkylblad och klistrar sedan in dessa kopierade rader i det nya arket.

Sub CopyFilteredRows () Dim rng As Range Dim ws As Worksheet If Worksheets ("Sheet1"). AutoFilterMode = False Then MsgBox "Det finns inga filtrerade rader" Exit Sub End If Set rng = Worksheets ("Sheet1"). AutoFilter.Range Set ws = Worksheets.Add rng.Copy Range ("A1") End Sub

Ovanstående kod skulle kontrollera om det finns några filtrerade rader i Sheet1 eller inte.

Om det inte finns några filtrerade rader visas en meddelanderuta som anger det.

Och om det finns filtrerade rader, kommer det att kopiera dem, infoga ett nytt kalkylblad och klistra in dessa rader på det nyligen infogade kalkylbladet.

Exempel: Filtrera data baserat på ett cellvärde

Med Autofilter i VBA tillsammans med en rullgardinsmeny kan du skapa en funktionalitet där alla poster för det objektet filtreras så snart du väljer ett objekt från rullgardinsmenyn.

Något som visas nedan:

Klicka här för att ladda ner exempelfilen och följa med.

Denna typ av konstruktion kan vara användbar när du snabbt vill filtrera data och sedan använda den vidare i ditt arbete.

Nedan är koden som gör detta:

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

Detta är en händelsekod för kalkylbladet, som bara körs när det sker en ändring i kalkylbladet och målcellen är B2 (där vi har rullgardinsmenyn).

Dessutom används ett If Then Else -villkor för att kontrollera om användaren har valt "Alla" från rullgardinsmenyn. Om Alla är markerade visas hela datamängden.

Denna kod är INTE placerad i en modul.

Istället måste den placeras i backend i kalkylbladet som har dessa data.

Här är stegen för att placera den här koden i kalkylbladets kodfönster:

1. Öppna VB Editor (kortkommando - ALT + F11).
2. I rutan Projektutforskare dubbelklickar du på det kalkylbladsnamn där du vill ha denna filtreringsfunktion.
3. Kopiera och klistra in ovanstående kod i kalkylbladets kodfönster.
4. Stäng VB -redigeraren.

När du nu använder listrutan filtrerar den automatiskt data.

Detta är en händelsekod för kalkylbladet, som bara körs när det sker en ändring i kalkylbladet och målcellen är B2 (där vi har rullgardinsmenyn).

Dessutom används ett If Then Else -villkor för att kontrollera om användaren har valt "Alla" från rullgardinsmenyn. Om Alla är markerade visas hela datamängden.

Slå på/av Excel AutoFilter med VBA

När Autofilter appliceras på ett antal celler kan det redan finnas några filter på plats.

Du kan använda koden nedan för att stänga av alla förapplicerade autofilter:

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

Denna kod kontrollerar hela arken och tar bort alla filter som har använts.

Om du inte vill stänga av filter från hela arket utan bara från en specifik datamängd, använd nedanstående kod:

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

Ovanstående kod kontrollerar om det redan finns filter på plats eller inte.

Om filter redan tillämpas tar det bort det, annars gör det ingenting.

På samma sätt, om du vill slå på AutoFilter, använder du koden nedan:

Sub TurnOnAutoFilter () If Not Worksheets ("Sheet1"). Range ("A4"). AutoFilter Then Worksheets ("Sheet1"). Range ("A4"). AutoFilter End If End Sub

Kontrollera om autofilter redan har tillämpats

Om du har ett blad med flera datamängder och du vill se till att du vet att det inte finns några filter redan kan du använda koden nedan.

Sub CheckforFilters () If ActiveSheet.AutoFilterMode = True Then MsgBox "Det finns filter redan på plats" Else MsgBox "Det finns inga filter" End If End Sub

Denna kod använder en meddelandeboxfunktion som visar meddelandet "Det finns filter redan på plats" när det hittar filter på arket, annars visar det "Det finns inga filter".

Visa all data

Om du har filter applicerade på datamängden och du vill visa all data använder du koden nedan:

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

Koden ovan kontrollerar om FilterMode är SANT eller FALSKT.

Om det är sant betyder det att ett filter har tillämpats och att det använder ShowAllData -metoden för att visa all data.

Observera att detta inte tar bort filtren. Filterikonerna är fortfarande tillgängliga för användning.

Använda autofilter på skyddade ark

Som standard, när du skyddar ett ark, fungerar inte filtren.

Om du redan har filter på plats kan du aktivera AutoFilter för att se till att det fungerar även på skyddade ark.

För att göra detta, markera alternativet Använd autofilter samtidigt som du skyddar arket.

Även om detta fungerar när du redan har filter på plats, fungerar det inte om du försöker lägga till autofilter med en VBA -kod.

Eftersom arket är skyddat tillåter det inte att något makro körs och gör ändringar i autofiltret.

Så du måste använda en kod för att skydda kalkylbladet och se till att automatiska filter är aktiverade i det.

Detta kan vara användbart när du har skapat ett dynamiskt filter (något som jag täckte i exemplet - "Filterdata baserat på ett cellvärde").

Nedan finns koden som skyddar arket, men samtidigt låter dig använda såväl filter som VBA -makron i det.

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

Denna kod måste placeras i ThisWorkbook -kodfönstret.

Här är stegen för att placera koden i ThisWorkbook -kodfönstret:

1. Öppna VB Editor (kortkommando - ALT + F11).
2. Dubbelklicka på ThisWorkbook-objektet i fönstret Project Explorer.
3. Kopiera och klistra in ovanstående kod i kodfönstret som öppnas.

Så snart du öppnar arbetsboken och aktiverar makron körs makrot automatiskt och skyddar Sheet1.

Innan du gör det kommer det dock att specificera 'EnableAutoFilter = True', vilket innebär att filtren också fungerar i det skyddade arket.

Det ställer också in argumentet 'UserInterfaceOnly' till 'True'. Det betyder att medan kalkylbladet är skyddat skulle VBA -makrokoden fortsätta att fungera.

Du kanske också gillar följande VBA -handledning:

• Excel VBA -slingor.
• Filtrera celler med fet teckenformatering.
• Spela in ett makro.
• Sortera data med hjälp av VBA.
• Sortera kalkylbladflikar i Excel.