Excel VBA Autofilter: A Complete Guide with Examples

Molte funzionalità di Excel sono disponibili anche per essere usate in VBA – e il metodo Autofilter è una di queste funzionalità.

Se hai un set di dati e vuoi filtrarlo usando un criterio, puoi farlo facilmente usando l’opzione Filter nella barra multifunzione Data.

E se vuoi una versione più avanzata, c’è anche un filtro avanzato in Excel.

Perché allora usare il filtro automatico in VBA?

Se hai solo bisogno di filtrare i dati e fare alcune cose di base, ti consiglierei di attenerti alla funzionalità di filtro integrata che offre l’interfaccia di Excel.

Dovresti usare il filtro automatico VBA quando vuoi filtrare i dati come parte della tua automazione (o se ti aiuta a risparmiare tempo rendendo più veloce il filtro dei dati).

Per esempio, supponiamo che tu voglia filtrare velocemente i dati basati su una selezione a discesa, e poi copiare questi dati filtrati in un nuovo foglio di lavoro.

Mentre questo può essere fatto usando la funzionalità di filtro incorporata con un po’ di copia-incolla, ti può richiedere molto tempo farlo manualmente.

In un tale scenario, usare VBA Autofilter può velocizzare le cose e risparmiare tempo.

Nota: tratterò questo esempio (sul filtraggio dei dati basato su una selezione a discesa e la copia in un nuovo foglio) più avanti in questo tutorial.

Sintassi del filtro automatico Excel VBA

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

• Espressione: Questo è l’intervallo su cui si vuole applicare il filtro automatico.
• Campo: Questo è il numero della colonna che vuoi filtrare. Questo viene contato da sinistra nel set di dati. Quindi se vuoi filtrare i dati in base alla seconda colonna, questo valore sarebbe 2.
• Criteri1: Questo è il criterio in base al quale vuoi filtrare il dataset.
• Operatore: Nel caso tu stia usando anche il criterio 2, puoi combinare questi due criteri in base all’Operatore. Sono disponibili i seguenti operatori: xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlBottom10Percent, xlTop10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues
• Criteria2: Questo è il secondo criterio sul quale puoi filtrare il dataset.
• VisibleDropDown: Puoi specificare se vuoi che l’icona a discesa del filtro appaia o meno nelle colonne filtrate. Questo argomento può essere VERO o FALSO.

A parte Expression, tutti gli altri argomenti sono opzionali.

Nel caso in cui non usiate alcun argomento, applicherà o rimuoverà semplicemente le icone dei filtri alle colonne.

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

Il codice sopra riportato applicherà semplicemente il metodo Autofilter alle colonne (o se è già applicato, lo rimuoverà).

Questo significa semplicemente che se non potete vedere le icone dei filtri nelle intestazioni delle colonne, comincerete a vederle quando questo codice viene eseguito, e se potete vederle, allora verranno rimosse.

Nel caso in cui abbiate dei dati filtrati, rimuoverà i filtri e vi mostrerà il set di dati completo.

Ora vediamo alcuni esempi di utilizzo di Excel VBA Autofilter che ne chiariranno l’uso.

Esempio: Filtrare i dati sulla base di una condizione di testo

Supponiamo di avere un set di dati come mostrato di seguito e di volerlo filtrare sulla base della colonna ‘Item’.

Il codice seguente filtrerebbe tutte le righe dove l’item è ‘Printer’.

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

Il codice qui sopra si riferisce al Foglio1 e all’interno di esso, si riferisce a A1 (che è una cella del dataset).

Nota che qui abbiamo usato Campo:=2, poiché la colonna item è la seconda colonna del nostro dataset da sinistra.

Ma prima di mostrarteli, lascia che ti faccia qualche esempio per mostrarti cosa può fare il metodo AutoFilter.

Clicca qui per scaricare il file di esempio e seguirlo.

Esempio: Criteri multipli (AND/OR) nella stessa colonna

Supponiamo che io abbia lo stesso set di dati, e questa volta voglio filtrare tutti i record dove l’elemento è o ‘Printer’ o ‘Projector’.

Il codice qui sotto farebbe questo:

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

Nota che qui ho usato l’operatore xlOR.

Questo dice a VBA di usare entrambi i criteri e filtrare i dati se uno dei due criteri è soddisfatto.

Similmente, potete anche usare i criteri AND.

Per esempio, se volete filtrare tutti i record dove la quantità è più di 10 ma meno di 20, potete usare il seguente codice:

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

Esempio: Criteri multipli con colonne diverse

Supponiamo che tu abbia il seguente set di dati.

Con Autofilter, puoi filtrare più colonne allo stesso tempo.

Per esempio, se vuoi filtrare tutti i record dove l’articolo è ‘Printer’ e il rappresentante è ‘Mark’, puoi usare il seguente codice:

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

Esempio: Filtrare i primi 10 record usando il metodo AutoFilter

Supponiamo che tu abbia il seguente set di dati.

Di seguito il codice che ti darà i primi 10 record (basati sulla colonna quantità):

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

Nel codice sopra, ho usato ActiveSheet. Potete usare il nome del foglio se volete.

Nota che in questo esempio, se vuoi ottenere i primi 5 elementi, basta cambiare il numero in Criteria1:=”10″ da 10 a 5.

Così per i primi 5 elementi, il codice sarebbe:

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

Può sembrare strano, ma non importa quanti primi elementi vuoi, il valore operatore rimane sempre xlTop10Items.

Similmente, il codice seguente vi darà i 10 elementi inferiori:

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

E se volete i 5 elementi inferiori, cambiate il numero in Criteria1:=”10″ da 10 a 5.

Esempio: Filtrare il 10% superiore usando il metodo AutoFilter

Supponiamo di avere lo stesso set di dati (usato negli esempi precedenti).

Di seguito il codice che vi darà i primi 10 record per cento (basati sulla colonna quantità):

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

Nel nostro set di dati, poiché abbiamo 20 record, restituirà i primi 2 record (che è il 10% dei record totali).

Esempio: Utilizzo dei caratteri jolly nel filtro automatico

Supponiamo di avere un set di dati come mostrato qui sotto:

Se volete filtrare tutte le righe in cui il nome dell’articolo contiene la parola ‘Board’, potete utilizzare il seguente codice:

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

Nel codice sopra, ho usato il carattere jolly * (asterisco) prima e dopo la parola ‘Board’ (che è il criterio).

Un asterisco può rappresentare qualsiasi numero di caratteri. Quindi questo filtrerebbe qualsiasi elemento che ha la parola ‘board’ in esso.

Esempio: Copiare le righe filtrate in un nuovo foglio

Se vuoi non solo filtrare i record basati su criteri ma anche copiare le righe filtrate, puoi usare la seguente macro.

Copia le righe filtrate, aggiunge un nuovo foglio di lavoro e poi incolla queste righe copiate nel nuovo foglio.

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

Il codice di cui sopra controlla se ci sono righe filtrate nel foglio1 o no.

Se non ci sono righe filtrate, mostrerà una casella di messaggio che lo dichiara.

E se ci sono righe filtrate, le copierà, inserirà un nuovo foglio di lavoro e incollerà queste righe nel nuovo foglio di lavoro inserito.

Esempio: Filtrare i dati in base al valore di una cella

Utilizzando Autofilter in VBA insieme a un elenco a discesa, è possibile creare una funzionalità in cui non appena si seleziona un elemento dall’elenco a discesa, tutti i record per quell’elemento vengono filtrati.

Come mostrato di seguito:

Clicca qui per scaricare il file di esempio e seguirlo.

Questo tipo di costrutto può essere utile quando volete filtrare rapidamente i dati e poi usarli ulteriormente nel vostro lavoro.

Di seguito il codice che farà questo:

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

Questo è un codice evento del foglio di lavoro, che viene eseguito solo quando c’è un cambiamento nel foglio di lavoro e la cella di destinazione è B2 (dove abbiamo il drop-down).

Inoltre, una condizione If Then Else viene usata per controllare se l’utente ha selezionato ‘All’ dal drop-down. Se Tutto è selezionato, viene mostrata l’intera serie di dati.

Questo codice NON è posto in un modulo.

Invece, deve essere posto nel backend del foglio di lavoro che ha questi dati.

Questi sono i passi per mettere questo codice nella finestra del codice del foglio di lavoro:

1. Aprire l’editor VB (scorciatoia da tastiera – ALT + F11).
2. Nel pannello Project Explorer, fare doppio clic sul nome del foglio di lavoro in cui si desidera questa funzionalità di filtro.
3. Nella finestra del codice del foglio di lavoro, copiate e incollate il codice di cui sopra.
4. Chiudete l’editor VB.

Ora quando usate l’elenco a discesa, filtrerà automaticamente i dati.

Questo è un codice evento del foglio di lavoro, che viene eseguito solo quando c’è un cambiamento nel foglio di lavoro e la cella di destinazione è B2 (dove abbiamo l’elenco a discesa).

Inoltre, una condizione If Then Else viene usata per controllare se l’utente ha selezionato ‘All’ dall’elenco a discesa. Se Tutto è selezionato, viene mostrato l’intero set di dati.

Attiva/Disattiva il filtro automatico di Excel usando VBA

Quando si applica il filtro automatico a un intervallo di celle, ci possono essere già alcuni filtri in atto.

Puoi usare il codice seguente per disattivare qualsiasi filtro automatico pre-applicato:

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

Questo codice controlla l’intero foglio e rimuove qualsiasi filtro che è stato applicato.

Se non volete disattivare i filtri dall’intero foglio ma solo da uno specifico set di dati, usate il codice seguente:

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

Il codice qui sopra controlla se ci sono già filtri in atto o meno.

Se i filtri sono già applicati, li rimuove, altrimenti non fa nulla.

Similmente, se volete attivare AutoFilter, usate il codice seguente:

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

Controlla se AutoFilter è già applicato

Se avete un foglio con più dataset e volete essere sicuri di sapere che non ci sono filtri già in atto, potete usare il codice seguente.

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

Questo codice usa una funzione di casella di messaggio che mostra un messaggio ‘Ci sono filtri già presenti’ quando trova dei filtri sul foglio, altrimenti mostra ‘Non ci sono filtri’.

Mostra tutti i dati

Se hai dei filtri applicati al dataset e vuoi mostrare tutti i dati, usa il codice seguente:

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

Il codice sopra controlla se FilterMode è TRUE o FALSE.

Se è vero, significa che è stato applicato un filtro e usa il metodo ShowAllData per mostrare tutti i dati.

Nota che questo non rimuove i filtri. Le icone dei filtri sono ancora disponibili per essere usate.

Usare AutoFilter su fogli protetti

Di default, quando proteggi un foglio, i filtri non funzionano.

Nel caso tu abbia già dei filtri in atto, puoi abilitare AutoFilter per assicurarti che funzioni anche su fogli protetti.

Per fare questo, seleziona l’opzione Use Autofilter mentre proteggi il foglio.

Mentre questo funziona quando hai già dei filtri in atto, nel caso in cui provi ad aggiungere Autofilters usando un codice VBA, non funzionerà.

Siccome il foglio è protetto, non permetterebbe a nessuna macro di essere eseguita e di apportare modifiche all’Autofilter.

Quindi devi usare un codice per proteggere il foglio di lavoro e assicurarti che i filtri automatici siano abilitati in esso.

Questo può essere utile quando hai creato un filtro dinamico (qualcosa che ho trattato nell’esempio – ‘Filtrare i dati in base al valore di una cella’).

Di seguito è riportato il codice che proteggerà il foglio, ma allo stesso tempo, vi permetterà di utilizzare i filtri e le macro VBA in esso.

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

Questo codice deve essere inserito nella finestra del codice di ThisWorkbook.

Questi sono i passi per mettere il codice nella finestra del codice di ThisWorkbook:

1. Aprire il VB Editor (scorciatoia da tastiera – ALT + F11).
2. Nel pannello Project Explorer, fare doppio clic sull’oggetto ThisWorkbook.
3. Nella finestra di codice che si apre, copiate e incollate il codice di cui sopra.

Appena aprite la cartella di lavoro e abilitate le macro, la macro verrà eseguita automaticamente e proteggerà il foglio1.

Tuttavia, prima di farlo, specificherà ‘EnableAutoFilter = True’, il che significa che i filtri funzioneranno anche nel foglio protetto.

Inoltre, imposta l’argomento ‘UserInterfaceOnly’ su ‘True’. Questo significa che mentre il foglio di lavoro è protetto, il codice delle macro VBA continuerà a funzionare.

Ti possono piacere anche i seguenti tutorial VBA:

• Circuiti VBA di Excel.
• Filtrare le celle con la formattazione del carattere in grassetto.
• Registrazione di una macro.
• Ordinare i dati utilizzando VBA.
• Ordinare le schede del foglio di lavoro in Excel.