Molte funzionalità di Excel sono disponibili anche per essere utilizzate in VBA - e il Filtro automatico metodo è una di queste funzionalità.
Se disponi di un set di dati e desideri filtrarlo utilizzando un criterio, puoi farlo facilmente utilizzando l'opzione Filtro nella barra dei dati.
E se vuoi una versione più avanzata, c'è anche un filtro avanzato in Excel.
Allora perché anche usare il filtro automatico in VBA?
Se hai solo bisogno di filtrare i dati e fare alcune cose di base, ti consiglio di attenersi alla funzionalità di filtro integrata offerta dall'interfaccia di Excel.
Dovresti usare VBA Autofilter quando vuoi filtrare i dati come parte della tua automazione (o se ti aiuta a risparmiare tempo rendendo più veloce il filtraggio dei dati).
Si supponga, ad esempio, di voler filtrare rapidamente i dati in base a una selezione a discesa e quindi copiare questi dati filtrati in un nuovo foglio di lavoro.
Sebbene ciò possa essere fatto utilizzando la funzionalità di filtro integrata insieme ad alcuni copia-incolla, può richiedere molto tempo per farlo manualmente.
In uno scenario del genere, l'utilizzo di VBA Autofilter può velocizzare le cose e risparmiare tempo.
Nota: tratterò questo esempio (sul filtraggio dei dati in base a una selezione a discesa e sulla copia in un nuovo foglio) più avanti in questo tutorial.
Sintassi del filtro automatico VBA di Excel
Espressione. Filtro automatico( _Campo_ , _Criteri1_ , _Operatore_ , _Criteri2_ , _VisibleDropDown_ )
- Espressione: questo è l'intervallo su cui si desidera applicare il filtro automatico.
- Campo: [Argomento facoltativo] Questo è il numero di 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: [Argomento facoltativo] Questi sono i criteri in base ai quali si desidera filtrare il set di dati.
- Operatore: [Argomento facoltativo] Nel caso in cui utilizzi 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
- Criteri2: [Argomento facoltativo] Questo è il secondo criterio in base al quale è possibile filtrare il set di dati.
- Visibile DropDown: [Argomento facoltativo] È possibile specificare se si desidera che l'icona a discesa del filtro venga visualizzata o meno nelle colonne filtrate. Questo argomento può essere VERO o FALSO.
A parte Expression, tutti gli altri argomenti sono facoltativi.
Nel caso in cui non utilizzi alcun argomento, applicherebbe o rimuoverà semplicemente le icone del filtro alle colonne.
Sub FilterRows() Fogli di lavoro ("Filtro dati"). Intervallo ("A1"). Filtro automatico Fine Sub
Il codice sopra applicherebbe semplicemente il metodo Autofilter alle colonne (o se è già applicato, lo rimuoverà).
Ciò significa semplicemente che se non riesci a vedere le icone del filtro nelle intestazioni delle colonne, inizierai a vederlo quando questo codice sopra viene eseguito e, se puoi vederlo, verrà rimosso.
Nel caso in cui disponi di dati filtrati, rimuoverà i filtri e ti mostrerà il set di dati completo.
Ora vediamo alcuni esempi di utilizzo di Excel VBA Autofilter che ne renderanno chiaro l'utilizzo.
Esempio: filtraggio dei dati in base a una condizione di testo
Supponiamo di avere un set di dati come mostrato di seguito e di volerlo filtrare in base alla colonna "Articolo".
Il codice seguente filtrerà tutte le righe in cui l'elemento è "Stampante".
Sub FilterRows() Fogli di lavoro ("Foglio1"). Intervallo ("A1"). Campo filtro automatico: = 2, Criteri 1: = "Stampante" End Sub
Il codice sopra si riferisce a Sheet1 e al suo interno si riferisce ad A1 (che è una cella nel set di dati).
Nota che qui abbiamo usato Field:=2, poiché la colonna dell'elemento è la seconda colonna nel nostro set di dati da sinistra.
Ora, se stai pensando, perché devo farlo usando un codice VBA. Questo può essere fatto facilmente utilizzando la funzionalità di filtro integrata.
Hai ragione!
Se questo è tutto ciò che vuoi fare, usa meglio la funzionalità di filtro integrata.
Ma mentre leggi il tutorial rimanente, vedrai che questo può essere combinato con del codice extra per creare una potente automazione.
Ma prima di mostrarvi quelli, lasciatemi prima coprire alcuni esempi per mostrarvi cosa può fare tutto il metodo AutoFilter.
Clicca qui per scaricare il file di esempio e seguire.
Esempio: criteri multipli (AND/OR) nella stessa colonna
Supponiamo di avere lo stesso set di dati e questa volta voglio filtrare tutti i record in cui l'elemento è "Stampante" o "Proiettore".
Il codice seguente farebbe questo:
Sub FilterRowsOR() Fogli di lavoro ("Foglio1"). Intervallo ("A1"). Campo filtro automatico: = 2, Criteri 1: = "Stampante", Operatore: = xlOr, Criteri 2: = "Proiettore" End Sub
Nota che qui ho usato il xlOR operatore.
Ciò indica a VBA di utilizzare entrambi i criteri e di filtrare i dati se uno dei due criteri è soddisfatto.
Allo stesso modo, puoi anche utilizzare i criteri AND.
Ad esempio, se desideri filtrare tutti i record in cui la quantità è maggiore di 10 ma minore di 20, puoi utilizzare il codice seguente:
Sub FilterRowsAND() Worksheets("Sheet1").Range("A1").AutoFilter Field:=4, Criteria1:=">10", _ Operator:=xlAnd, Criteria2:="<20" End Sub
Esempio: criteri multipli con colonne diverse
Supponiamo di avere il seguente set di dati.
Con Filtro automatico, puoi filtrare più colonne contemporaneamente.
Ad esempio, se si desidera filtrare tutti i record in cui l'articolo è "Stampante" e il rappresentante di vendita è "Mark", è possibile utilizzare il codice seguente:
Sub FilterRows() With Worksheets("Sheet1").Range("A1") .AutoFilter field:=2, Criteria1:="Stampante" .AutoFilter field:=3, Criteria1:="Mark" End With End Sub
Esempio: filtrare i primi 10 record utilizzando il metodo di filtro automatico
Supponiamo di avere il set di dati seguente.
Di seguito è riportato il codice che ti darà i primi 10 record (in base alla colonna quantità):
Sub FilterRowsTop10() ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="10", Operator:=xlTop10Items End Sub
Nel codice sopra, ho usato ActiveSheet. Se lo desideri, puoi utilizzare il nome del foglio.
Nota che in questo esempio, se vuoi ottenere i primi 5 elementi, cambia semplicemente il numero in Criteri 1:=”10″ dalle 10 alle 5.
Quindi per i primi 5 articoli, il codice sarebbe:
Sub FilterRowsTop5() ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="5", Operator:=xlTop10Items End Sub
Può sembrare strano, ma non importa quanti elementi principali desideri, il valore dell'operatore rimane sempre xlTop10Items.
Allo stesso modo, il codice seguente ti darebbe gli ultimi 10 elementi:
Sub FilterRowsBottom10() ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="10", Operator:=xlBottom10Items End Sub
E se vuoi gli ultimi 5 elementi, cambia il numero in Criteri 1:=”10″ dalle 10 alle 5.
Esempio: filtrare il 10% superiore utilizzando il metodo di filtro automatico
Supponiamo di avere lo stesso set di dati (come usato negli esempi precedenti).
Di seguito è riportato il codice che ti fornirà i record del 10% superiore (in base alla colonna della quantità):
Sub FilterRowsTop10() ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="10", Operator:=xlTop10Percent End Sub
Nel nostro set di dati, poiché abbiamo 20 record, restituirà i primi 2 record (che è il 10% dei record totali).
Esempio: utilizzo di caratteri jolly nel filtro automatico
Supponiamo di avere un set di dati come mostrato di seguito:
Se desideri filtrare tutte le righe in cui il nome dell'articolo contiene la parola "Board", puoi utilizzare il codice seguente:
Sub FilterRowsWildcard() Fogli di lavoro("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 un numero qualsiasi di caratteri. Quindi questo filtrerebbe qualsiasi elemento che contenga la parola "scheda".
Esempio: copia le righe filtrate in un nuovo foglio
Se desideri non solo filtrare i record in base a criteri, ma anche copiare le righe filtrate, puoi utilizzare la macro seguente.
Copia le righe filtrate, aggiunge un nuovo foglio di lavoro e quindi incolla queste righe copiate nel nuovo foglio.
Sub CopyFilteredRows() Dim rng As Range Dim ws As Worksheet If Worksheets("Sheet1").AutoFilterMode = False Then MsgBox "Non ci sono righe filtrate" Exit Sub End If Set rng = Worksheets("Sheet1").AutoFilter.Range Set ws = Worksheets.Add rng.Copy Range("A1") End Sub
Il codice sopra controlla se ci sono righe filtrate in Sheet1 o meno.
Se non ci sono righe filtrate, mostrerà una finestra di messaggio che lo afferma.
E se ci sono righe filtrate, le copierà, inserirà un nuovo foglio di lavoro e incollerà queste righe su quel foglio di lavoro appena inserito.
Esempio: filtrare i dati in base a un valore di cella
Utilizzando il filtro automatico in VBA insieme a un elenco a discesa, puoi creare una funzionalità in cui non appena selezioni un elemento dal menu a discesa, tutti i record per quell'elemento vengono filtrati.
Qualcosa come mostrato di seguito:
Clicca qui per scaricare il file di esempio e seguire.
Questo tipo di costrutto può essere utile quando si desidera filtrare rapidamente i dati e utilizzarli ulteriormente nel proprio lavoro.
Di seguito è riportato il codice che lo farà:
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, Criterio1:=Intervallo("B2") End If End If End Sub
Questo è un codice evento del foglio di lavoro, che viene eseguito solo quando c'è una modifica nel foglio di lavoro e la cella di destinazione è B2 (dove abbiamo il menu a discesa).
Inoltre, viene utilizzata una condizione If Then Else per verificare se l'utente ha selezionato "Tutto" dal menu a discesa. Se è selezionato Tutto, viene mostrato l'intero set di dati.
Questo codice NON è inserito in un modulo.
Invece, deve essere posizionato nel backend del foglio di lavoro che contiene questi dati.
Ecco i passaggi per inserire questo codice nella finestra del codice del foglio di lavoro:
- Apri l'editor VB (scorciatoia da tastiera - ALT + F11).
- Nel riquadro Esplora progetti, fare doppio clic sul nome del foglio di lavoro in cui si desidera questa funzionalità di filtro.
- Nella finestra del codice del foglio di lavoro, copia e incolla il codice sopra.
- Chiudi l'editor VB.
Ora quando usi l'elenco a discesa, filtrerà automaticamente i dati.
Questo è un codice evento del foglio di lavoro, che viene eseguito solo quando c'è una modifica nel foglio di lavoro e la cella di destinazione è B2 (dove abbiamo il menu a discesa).
Inoltre, viene utilizzata una condizione If Then Else per verificare se l'utente ha selezionato "Tutto" dal menu a discesa. Se è selezionato Tutto, viene mostrato l'intero set di dati.
Attiva/disattiva il filtro automatico di Excel utilizzando VBA
Quando si applica il filtro automatico a un intervallo di celle, potrebbero essere già presenti alcuni filtri.
Puoi utilizzare il codice seguente per disattivare eventuali filtri automatici pre-applicati:
Sub TurnOFFAutoFilter() Worksheets("Sheet1").AutoFilterMode = False End Sub
Questo codice controlla l'intero foglio e rimuove i filtri applicati.
Se non desideri disattivare i filtri dall'intero foglio ma solo da un set di dati specifico, utilizza il codice seguente:
Sub TurnOFFAutoFilter() If Worksheets("Sheet1").Range("A1").AutoFilter Then Worksheets("Sheet1").Range("A1").AutoFilter End If End Sub
Il codice sopra controlla se ci sono già filtri in atto o meno.
Se i filtri sono già applicati, lo rimuove, altrimenti non fa nulla.
Allo stesso modo, se vuoi attivare il filtro automatico, usa il codice seguente:
Sub TurnOnAutoFilter() If Not Worksheets("Sheet1").Range("A4").AutoFilter Then Worksheets("Sheet1").Range("A4").AutoFilter End If End Sub
Controlla se il filtro automatico è già applicato
Se disponi di un foglio con più set di dati e vuoi assicurarti di sapere che non sono già presenti filtri, puoi utilizzare il codice seguente.
Sub CheckforFilters() If ActiveSheet.AutoFilterMode = True Then MsgBox "Sono già presenti filtri" Else MsgBox "Non ci sono filtri" End If End Sub
Questo codice utilizza una funzione di casella di messaggio che visualizza un messaggio "Sono presenti filtri già presenti" quando trova filtri sul foglio, altrimenti mostra "Non ci sono filtri".
Mostra tutti i dati
Se hai dei filtri applicati al set di dati e vuoi mostrare tutti i dati, usa il codice seguente:
Sub ShowAllData() If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End Sub
Il codice sopra controlla se FilterMode è TRUE o FALSE.
Se è vero, significa che è stato applicato un filtro e utilizza il metodo ShowAllData per mostrare tutti i dati.
Notare che questo non rimuove i filtri. Le icone dei filtri sono ancora disponibili per essere utilizzate.
Utilizzo del filtro automatico sui fogli protetti
Per impostazione predefinita, quando proteggi un foglio, i filtri non funzioneranno.
Nel caso in cui disponi già di filtri, puoi abilitare AutoFilter per assicurarti che funzioni anche su fogli protetti.
Per fare ciò, seleziona l'opzione Usa filtro automatico proteggendo il foglio.
Sebbene funzioni quando sono già presenti filtri, nel caso in cui provi ad aggiungere filtri automatici utilizzando un codice VBA, non funzionerà.
Poiché il foglio è protetto, non consente l'esecuzione di alcuna macro e apporta modifiche al filtro automatico.
Quindi è necessario utilizzare un codice per proteggere il foglio di lavoro e assicurarsi che i filtri automatici siano abilitati al suo interno.
Questo può essere utile quando hai creato un filtro dinamico (qualcosa che ho trattato nell'esempio - "Filtra i dati in base a un valore di cella").
Di seguito è riportato il codice che proteggerà il foglio, ma allo stesso tempo ti consentirà di utilizzare i filtri e le macro VBA al suo interno.
Private Sub Workbook_Open() With Worksheets("Sheet1") .EnableAutoFilter = True .Protect Password:="password", Contents:=True, UserInterfaceOnly:=True End With End Sub
Questo codice deve essere inserito nella finestra del codice ThisWorkbook.
Ecco i passaggi per inserire il codice nella finestra del codice di ThisWorkbook:
- Apri l'editor VB (scorciatoia da tastiera - ALT + F11).
- Nel riquadro Esplora progetti, fare doppio clic sull'oggetto ThisWorkbook.
- Nella finestra del codice che si apre, copia e incolla il codice sopra.
Non appena apri la cartella di lavoro e abiliti le macro, la macro verrà eseguita automaticamente e proteggerà Sheet1.
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". Ciò significa che mentre il foglio di lavoro è protetto, il codice delle macro VBA continuerà a funzionare.
Potrebbero piacerti anche i seguenti tutorial VBA:
- Cicli VBA di Excel.
- Filtra le celle con la formattazione dei caratteri in grassetto.
- Registrazione di una macro.
- Ordina i dati utilizzando VBA.
- Ordina le schede del foglio di lavoro in Excel.
