Excel VBA Autofilter:

En hel del Excel-funktioner kan också användas i VBA – och Autofilter-metoden är en sådan funktion.

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

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

Så varför ens använda Autofilter i VBA?

Om du bara behöver filtrera data och göra några grundläggande saker skulle jag rekommendera att du håller dig till den inbyggda filterfunktionaliteten 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).

Antag till exempel att du snabbt vill filtrera data baserat på ett rullgardinsval och sedan kopiera dessa filtrerade data till ett nytt arbetsblad.

Sedan kan detta göras med hjälp av den inbyggda filterfunktionaliteten tillsammans med lite kopiera-klistra in, men det kan ta mycket tid att göra detta manuellt.

I ett sådant scenario kan du med hjälp av VBA Autofilter påskynda saker och ting och spara tid.

Notera: Jag kommer att täcka det här exemplet (om filtrering av data baserat på ett rullgardinsval och kopiering till ett nytt ark) senare i den här handledningen.

Excel VBA Autofilter Syntax

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

• Expression:
• Fält: Detta är det område som du vill tillämpa det automatiska filtret på.
• Fält: Detta är det område som du vill tillämpa det automatiska filtret på: Detta är kolumnnumret som du vill filtrera. Detta räknas från vänster i datasetet. Om du vill filtrera data baserat på den andra kolumnen är värdet 2.
• Kriterium1: Detta är det kriterium som du vill filtrera datasetet baserat på.
• Operatör: Om du använder kriterium 2 också kan du kombinera dessa två kriterier baserat på Operator. Följande operatörer kan användas: xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlBottom10Percent, xlTop10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues
• Kriterium2: Det här är det andra kriteriet som du kan filtrera datamängden på.
• VisibleDropDown: Du kan ange om du vill att ikonen för filterrullgardinsystemet ska visas i de filtrerade kolumnerna eller inte. Detta argument kan vara TRUE eller FALSE.

Avseendet Expression är alla andra argument valfria.

Om du inte använder något argument skulle det helt enkelt applicera eller ta bort filterikonen på kolumnerna.

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

Ovanstående kod skulle helt enkelt applicera Autofilter-metoden på kolumnerna (eller om den redan har applicerats, ta bort den).

Detta innebär helt enkelt att om du inte kan se filterikoner i kolumnrubrikerna, kommer du att börja se det när ovanstående kod exekveras, och om du kan se det, kommer det att tas bort.

Om du har några filtrerade data, kommer det att ta bort filtren och visa dig den fullständiga datamängden.

Nu ska vi se några exempel på hur man använder Excel VBA Autofilter som kommer att göra dess användning tydlig.

Exempel: Antag att du har ett dataset som visas nedan och du vill filtrera det baserat på kolumnen ”Item”.

Med nedanstående kod skulle du filtrera alla rader där objektet är ”Printer”.

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

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

Bemärk att vi här har använt Field:=2, eftersom kolumnen ”item” är den andra kolumnen i vårt dataset från vänster.

Men innan jag visar dig dessa, låt mig först ta upp några exempel för att visa dig vad allt AutoFilter-metoden kan göra.

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

Exempel:

Antag att jag har samma dataset och den här gången vill filtrera alla poster där objektet är antingen ”Printer” eller ”Projector”.

Med nedanstående kod kan detta göras:

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

Notera att jag här har använt xlOR-operatorn.

Detta säger till VBA att använda båda kriterierna och filtrera data om något av de två kriterierna uppfylls.

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

Om du till exempel vill filtrera alla poster där kvantiteten är mer än 10 men mindre än 20, kan du använda nedanstående kod:

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

Exempel:

Antag att du har följande dataset:

Med Autofilter kan du filtrera flera kolumner samtidigt.

Om du t.ex. vill filtrera alla poster där artikeln är ”Printer” och säljaren är ”Mark”, kan du använda nedanstående kod:

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

Exempel: Anta att du har nedanstående dataset:

Nedan följer koden som ger dig de 10 bästa posterna (baserat på kolumnen kvantitet):

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

I koden ovan har jag använt ActiveSheet. Du kan använda arkets namn om du vill.

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

Så för de fem bästa artiklarna skulle koden vara:

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

Det kan se konstigt ut, men oavsett hur många toppartiklar du vill ha förblir Operator-värdet alltid xlTop10Items.

Samma sak, koden nedan skulle ge dig de 10 nedersta objekten:

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

Och om du vill ha de 5 nedersta objekten ändrar du siffran i Criteria1:=”10″ från 10 till 5.

Exempel:

Antag att du har samma datamängd (som användes i de tidigare exemplen).

Nedan följer koden som ger dig de 10 procent bästa posterna (baserat på kolumnen kvantitet):

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

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

Exempel: Filtrera de 10 procent bästa posterna med hjälp av AutoFilter-metoden: Om du vill filtrera alla rader där artikelnamnet innehåller ordet ”Board” kan du använda följande kod:

Om du vill filtrera alla rader där artikelnamnet innehåller ordet ”Board” kan du använda nedanstående kod:

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

I koden ovan har jag använt jokerkortstecknet * (asterisk) före och efter ordet ”Board” (som är kriteriet).

En asterisk kan representera ett valfritt antal tecken. Detta skulle alltså filtrera alla objekt som innehåller ordet ”Board”.

Exempel: Om du inte bara vill filtrera posterna utifrån kriterier utan också kopiera de filtrerade raderna kan du använda nedanstående makro.

Det kopierar de filtrerade raderna, lägger till ett nytt arbetsblad och klistrar sedan in de kopierade raderna i det nya arbetsbladet.

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

Ovanstående kod kontrollerar om det finns några filtrerade rader i ark1 eller inte.

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

Och om det finns filtrerade rader kopieras dessa, ett nytt kalkylblad läggs till och dessa rader klistras in i det nyinsatta kalkylbladet.

Exempel:

Med hjälp av Autofilter i VBA tillsammans med en rullgardinslista kan du skapa en funktionalitet där alla poster för den posten filtreras så snart du väljer ett objekt i rullgardinslistan.

Något som visas nedan:

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

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

Nedan följer koden som gör detta:

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

Detta är en händelsekod för arbetsbladet, som endast utförs när det sker en ändring i arbetsbladet och målcellen är B2 (där vi har rullgardinsystemet).

Det används också ett If Then Else-villkor för att kontrollera om användaren har valt ”All” från rullgardinsystemet. Om All väljs visas hela datamängden.

Denna kod placeras INTE i en modul.

Istället måste den placeras i baksidan av arbetsbladet som har dessa data.

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

1. Öppna VB-redigeraren (tangentbordsgenväg – ALT + F11).
2. I fönstret Projektutforskare dubbelklickar du på arbetsbladets namn där du vill ha den här filtreringsfunktionen.
3. I arbetsbladets kodfönster kopierar och klistrar du in ovanstående kod.
4. Slut VB-editorn.

Nu när du använder rullgardinslistan filtrerar den automatiskt data.

Detta är en händelsekod för arbetsbladet, som endast utförs när det sker en ändring i arbetsbladet och målcellen är B2 (där vi har rullgardinslistan).

Det används också ett If Then Else-villkor för att kontrollera om användaren har valt ”All” från rullgardinslistan. Om All väljs visas hela datamängden.

Slå på Excel AutoFilter ON/OFF med hjälp av VBA

När du tillämpar Autofilter på ett cellområde kan det redan finnas några filter på plats.

Du kan använda nedanstående kod för att stänga av alla föranvända autofilter:

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

Den här koden kontrollerar hela arket och tar bort alla filter som har tillämpats.

Om du inte vill stänga av filter från hela arket utan bara från en specifik datauppsättning kan du använda nedanstående kod:

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

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

Om filter redan har tillämpats tar den bort det, annars gör den ingenting.

Om du på samma sätt vill aktivera AutoFilter använder du nedanstående kod:

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

Kontrollera om AutoFilter redan tillämpas

Om du har ett ark med flera datamängder och vill försäkra dig om att du vet att det inte redan finns några filter på plats, kan du använda nedanstående kod.

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

Den här koden använder en meddelanderuta som visar meddelandet ”Det finns redan filter på plats” när den hittar filter på arket, annars visar den ”Det finns inga filter”.

Visa alla data

Om du har tillämpat filter på datasetet och vill visa alla data använder du nedanstående kod:

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

Ovanstående kod kontrollerar om FilterMode är TRUE eller FALSE.

Om den är sann betyder det att ett filter har tillämpats och den använder metoden ShowAllData för att visa alla data.

Bemärk att detta inte tar bort filtren. Filtersymbolerna är fortfarande tillgängliga för användning.

Använda AutoFilter på skyddade ark

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

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

För att göra detta markerar du alternativet Använd autofilter när du skyddar arket.

Det här fungerar när du redan har filter på plats, men om du försöker lägga till autofilter med hjälp av en VBA-kod kommer det inte att fungera.

Då arket är skyddat kan inget makro köras och göra ändringar i autofiltret.

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

Detta kan vara användbart när du har skapat ett dynamiskt filter (något som jag tog upp i exemplet – ”Filtrera data baserat på ett cellvärde”).

Nedan följer koden som skyddar arket, men som samtidigt gör det möjligt att använda filter och VBA-makron i det.

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

Den här koden måste placeras i kodfönstret ThisWorkbook.

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

1. Öppna VB-redigeraren (tangentbordsgenväg – ALT + F11).
2. Du dubbelklickar på objektet ThisWorkbook i fönstret Project Explorer.
3. I kodfönstret som öppnas kopierar och klistrar du in ovanstående kod.

Så snart du öppnar arbetsboken och aktiverar makron kommer den att köra makrot automatiskt och skydda Sheet1.

Hursomhelst kommer den innan den gör det att ange ”EnableAutoFilter = True”, vilket innebär att filtren skulle fungera även i det skyddade bladet.

Det ställer också in argumentet ”UserInterfaceOnly” till ”True”. Detta innebär att medan arbetsbladet är skyddat kommer VBA-makrokoden att fortsätta att fungera.

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

• Excel VBA Loops.
• Filtrera celler med fetstilformatering.
• Spela in ett makro.
• Sortera data med hjälp av VBA.
• Sortera flikar på arbetsbladet i Excel.