Excel VBA Autofilter: A Complete Guide with Examples

O mulțime de funcționalități Excel sunt, de asemenea, disponibile pentru a fi utilizate în VBA – iar metoda Autofilter este una dintre aceste funcționalități.

Dacă aveți un set de date și doriți să îl filtrați folosind un criteriu, o puteți face cu ușurință folosind opțiunea Filter din panglica Data.

Și dacă doriți o versiune mai avansată a acesteia, există și un filtru avansat în Excel.

Atunci de ce să folosiți chiar și AutoFilterul în VBA?

Dacă aveți nevoie doar să filtrați datele și să faceți câteva lucruri de bază, v-aș recomanda să rămâneți la funcționalitatea de filtrare încorporată pe care o oferă interfața Excel.

Ar trebui să folosiți Autofilterul VBA atunci când doriți să filtrați datele ca parte a automatizării dumneavoastră (sau dacă vă ajută să economisiți timp, făcând mai rapidă filtrarea datelor).

De exemplu, să presupunem că doriți să filtrați rapid datele pe baza unei selecții derulante și apoi să copiați aceste date filtrate într-o nouă foaie de lucru.

În timp ce acest lucru se poate face utilizând funcționalitatea de filtrare încorporată împreună cu unele operațiuni de copy-paste, vă poate lua mult timp să faceți acest lucru manual.

Într-un astfel de scenariu, utilizarea VBA Autofilter poate accelera lucrurile și economisi timp.

Nota: Voi aborda acest exemplu (privind filtrarea datelor pe baza unei selecții derulante și copierea într-o foaie nouă) mai târziu în acest tutorial.

Sintaxa Excel VBA Autofilter

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

• Expresie: Acesta este intervalul pe care doriți să aplicați filtrul automat.
• Field: Acesta este numărul coloanei pe care doriți să o filtrați. Acesta este numărat de la stânga în setul de date. Deci, dacă doriți să filtrați datele pe baza celei de-a doua coloane, această valoare va fi 2.
• Criteria1: Acesta este criteriul pe baza căruia doriți să filtrați setul de date.
• Operator: În cazul în care utilizați și criteriul 2, puteți combina aceste două criterii pe baza Operatorului. Se pot utiliza următorii operatori: xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlBottom10Percent, xlTop10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues
• Criteria2: Acesta este cel de-al doilea criteriu în funcție de care puteți filtra setul de date.
• VisibleDropDown: Puteți specifica dacă doriți ca pictograma de filtrare să apară sau nu în coloanele filtrate. Acest argument poate fi TRUE sau FALSE.

În afară de Expression, toate celelalte argumente sunt opționale.

În cazul în care nu folosiți niciun argument, se va aplica sau elimina pur și simplu pictogramele de filtrare pe coloane.

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

Codul de mai sus va aplica pur și simplu metoda Autofilter pe coloane (sau, dacă este deja aplicată, o va elimina).

Acest lucru înseamnă pur și simplu că, dacă nu puteți vedea pictogramele de filtrare în antetele coloanelor, veți începe să le vedeți atunci când acest cod de mai sus este executat, iar dacă le puteți vedea, atunci vor fi eliminate.

În cazul în care aveți date filtrate, acesta va elimina filtrele și vă va afișa setul de date complet.

Acum să vedem câteva exemple de utilizare a Excel VBA Autofilter care vor face clară utilizarea acestuia.

Exemplu: Filtrarea datelor pe baza unei condiții de text

Să presupunem că aveți un set de date așa cum se arată mai jos și doriți să îl filtrați pe baza coloanei ‘Item’.

Codul de mai jos ar filtra toate rândurile în care elementul este ‘Printer’.

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

Codul de mai sus se referă la Sheet1 și, în cadrul acestuia, se referă la A1 (care este o celulă din setul de date).

Rețineți că aici am folosit Field:=2, deoarece coloana item este a doua coloană din setul nostru de date din stânga.

Dar înainte de a vi le arăta pe acelea, dați-mi voie mai întâi să cuprind câteva exemple pentru a vă arăta tot ce poate face metoda AutoFilter.

Click aici pentru a descărca fișierul de exemplu și pentru a urmări împreună.

Exemplu: Criterii multiple (AND/OR) în aceeași coloană

Să presupunem că am același set de date, iar de data aceasta vreau să filtrez toate înregistrările în care elementul este fie ‘Printer’, fie ‘Projector’.

Codul de mai jos ar face acest lucru:

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

Rețineți că aici am folosit operatorul xlOR.

Acest lucru îi spune lui VBA să utilizeze ambele criterii și să filtreze datele dacă oricare dintre cele două criterii sunt îndeplinite.

În mod similar, puteți utiliza și criteriul AND.

De exemplu, dacă doriți să filtrați toate înregistrările în care cantitatea este mai mare de 10 dar mai mică de 20, puteți utiliza codul de mai jos:

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

Exemplu: Criterii multiple cu coloane diferite

Să presupunem că aveți următorul set de date.

Cu Autofilter, puteți filtra mai multe coloane în același timp.

De exemplu, dacă doriți să filtrați toate înregistrările în care articolul este „Printer” și reprezentantul de vânzări este „Mark”, puteți utiliza codul de mai jos:

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

Exemplu: Filtrarea primelor 10 înregistrări folosind metoda AutoFilter

Să presupunem că aveți setul de date de mai jos.

Acesta este codul care vă va oferi primele 10 înregistrări (pe baza coloanei cantitate):

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

În codul de mai sus, am folosit ActiveSheet. Puteți folosi numele foii dacă doriți.

Rețineți că, în acest exemplu, dacă doriți să obțineți primele 5 articole, trebuie doar să schimbați numărul din Criteria1:=”10″ de la 10 la 5.

Deci, pentru primele 5 articole, codul ar fi:

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

Poate părea ciudat, dar indiferent de câte articole de top doriți, valoarea Operatorului rămâne întotdeauna xlTop10Items.

În mod similar, codul de mai jos v-ar oferi cele 10 elemente de jos:

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

Și dacă doriți cele 5 elemente de jos, schimbați numărul din Criteria1:=”10″ de la 10 la 5.

Exemplu: Filtrarea primelor 10 procente folosind metoda AutoFilter

Să presupunem că aveți același set de date (ca cel folosit în exemplele anterioare).

Mai jos este codul care vă va oferi primele 10 procente din înregistrări (pe baza coloanei cantitate):

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

În setul nostru de date, din moment ce avem 20 de înregistrări, acesta va returna primele 2 înregistrări (ceea ce reprezintă 10% din totalul înregistrărilor).

Exemplu: Utilizarea caracterelor wildcard în autofiltrul

Să presupunem că aveți un set de date așa cum se arată mai jos:

Dacă doriți să filtrați toate rândurile în care numele articolului conține cuvântul „Board”, puteți utiliza codul de mai jos:

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

În codul de mai sus, am utilizat caracterul wildcard * (asterisc) înainte și după cuvântul „Board” (care este criteriul).

Un asterisc poate reprezenta orice număr de caractere. Deci, acest lucru ar filtra orice element care are cuvântul ‘board’ în el.

Exemplu: Copierea rândurilor filtrate într-o foaie nouă

Dacă doriți nu numai să filtrați înregistrările pe baza criteriilor, ci și să copiați rândurile filtrate, puteți utiliza macroul de mai jos.

Copiază rândurile filtrate, adaugă o nouă foaie de lucru și apoi lipește aceste rânduri copiate în noua foaie.

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

Codul de mai sus va verifica dacă există sau nu rânduri filtrate în Foaia1.

Dacă nu există rânduri filtrate, va afișa o casetă de mesaj care să precizeze acest lucru.

Și dacă există rânduri filtrate, le va copia pe acestea, va introduce o nouă foaie de lucru și va lipi aceste rânduri pe acea foaie de lucru nou introdusă.

Exemplu: Filtrarea datelor pe baza unei valori de celulă

Utilizând Autofilter în VBA împreună cu o listă derulantă, puteți crea o funcționalitate în care, de îndată ce selectați un element din lista derulantă, toate înregistrările pentru acel element sunt filtrate.

Ca ceva de genul celor prezentate mai jos:

Click aici pentru a descărca fișierul de exemplu și pentru a urma instrucțiunile.

Acest tip de construcție poate fi util atunci când doriți să filtrați rapid datele și apoi să le folosiți mai departe în activitatea dumneavoastră.

Mai jos este codul care va face acest lucru:

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

Acesta este un cod de eveniment din foaia de lucru, care se execută numai atunci când există o modificare în foaia de lucru și celula țintă este B2 (unde avem lista derulantă).

De asemenea, o condiție If Then Else este folosită pentru a verifica dacă utilizatorul a selectat ‘All’ din lista derulantă. Dacă este selectat All, este afișat întregul set de date.

Acest cod NU este plasat într-un modul.

În schimb, trebuie plasat în backend-ul foii de calcul care are aceste date.

Iată care sunt pașii pentru a plasa acest cod în fereastra de cod a foii de lucru:

1. Deschideți editorul VB (comandă rapidă de la tastatură – ALT + F11).
2. În panoul Project Explorer, faceți dublu clic pe numele foii de lucru în care doriți această funcționalitate de filtrare.
3. În fereastra de cod a foii de lucru, copiați și lipiți codul de mai sus.
4. Închideți Editorul VB.

Acum, când utilizați lista derulantă, aceasta va filtra automat datele.

Acesta este un cod de eveniment din foaia de lucru, care se execută numai atunci când există o modificare în foaia de lucru și celula țintă este B2 (unde avem lista derulantă).

De asemenea, o condiție If Then Else este utilizată pentru a verifica dacă utilizatorul a selectat „All” din lista derulantă. Dacă este selectat All, este afișat întregul set de date.

Turn Excel AutoFilter ON/OFF using VBA

Când se aplică Autofilter la un interval de celule, este posibil să existe deja unele filtre în vigoare.

Puteți utiliza codul de mai jos pentru a dezactiva orice filtre automate pre-aplicate:

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

Acest cod verifică întreaga foaie și elimină orice filtre care au fost aplicate.

Dacă nu doriți să dezactivați filtrele din întreaga foaie, ci doar dintr-un anumit set de date, utilizați codul de mai jos:

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

Codul de mai sus verifică dacă există deja filtre aplicate sau nu.

Dacă sunt deja aplicate filtre, le elimină, altfel nu face nimic.

În mod similar, dacă doriți să activați AutoFilter, utilizați codul de mai jos:

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

Check if AutoFilter is Already Applied

Dacă aveți o foaie cu mai multe seturi de date și doriți să vă asigurați că știți că nu există filtre deja aplicate, puteți utiliza codul de mai jos.

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

Acest cod utilizează o funcție de căsuță de mesaje care afișează un mesaj „There are Filters already in place” (Există filtre deja aplicate) atunci când găsește filtre pe foaie, altfel afișează „There are no filters” (Nu există filtre).

Show All Data

Dacă aveți filtre aplicate la setul de date și doriți să afișați toate datele, utilizați codul de mai jos:

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

Codul de mai sus verifică dacă FilterMode este TRUE sau FALSE.

Dacă este true, înseamnă că a fost aplicat un filtru și folosește metoda ShowAllData pentru a afișa toate datele.

Rețineți că acest lucru nu elimină filtrele. Pictogramele de filtrare sunt încă disponibile pentru a fi utilizate.

Utilizarea filtrului automat pe foi protejate

În mod implicit, atunci când protejați o foaie, filtrele nu vor funcționa.

În cazul în care aveți deja filtre aplicate, puteți activa filtrul automat pentru a vă asigura că acesta funcționează chiar și pe foi protejate.

Pentru a face acest lucru, bifați opțiunea Use Autofilter în timp ce protejați foaia.

În timp ce acest lucru funcționează atunci când aveți deja filtre în vigoare, în cazul în care încercați să adăugați Autofiltre folosind un cod VBA, acesta nu va funcționa.

Din moment ce foaia este protejată, aceasta nu ar permite niciunui macro să ruleze și să facă modificări la Autofiltrul.

Așa că trebuie să folosiți un cod pentru a proteja foaia de lucru și să vă asigurați că filtrele automate sunt activate în ea.

Acest lucru poate fi util atunci când ați creat un filtru dinamic (ceva ce am acoperit în exemplul – ‘Filter Data based on a Cell Value’).

Mai jos este codul care va proteja foaia de calcul, dar, în același timp, vă va permite să folosiți Filtre precum și macro-uri VBA în ea.

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

Acest cod trebuie plasat în fereastra de cod ThisWorkbook.

Iată care sunt pașii pentru a plasa codul în fereastra de cod ThisWorkbook:

1. Deschideți editorul VB (comandă rapidă de la tastatură – ALT + F11).
2. În panoul Project Explorer, faceți dublu clic pe obiectul ThisWorkbook.
3. În fereastra de cod care se deschide, copiați și lipiți codul de mai sus.

De îndată ce deschideți registrul de lucru și activați macrourile, acesta va rula automat macroul și va proteja Sheet1.

Cu toate acestea, înainte de a face acest lucru, va specifica ‘EnableAutoFilter = True’, ceea ce înseamnă că filtrele ar funcționa și în foaia protejată.

De asemenea, setează argumentul ‘UserInterfaceOnly’ la ‘True’. Acest lucru înseamnă că, în timp ce foaia de lucru este protejată, codul macro VBA ar continua să funcționeze.

Vă pot plăcea și următoarele tutoriale VBA:

• Excel VBA Loops.
• Filtrarea celulelor cu formatarea caracterelor negre.
• Înregistrarea unui macro.
• Sortarea datelor folosind VBA.
• Sortarea filelor foii de lucru în Excel.

.