Tutorial Filtro Avanzato: come utilizzarlo in VBA - Parte 1

Stato
Chiusa ad ulteriori risposte.

giulianovac

Access/VBA Expert
Expert
9 Giugno 2018
1.969
83
Italy
2013 2019
142
Filtro Avanzato: come utilizzarlo in VBA - Parte 1


PREMESSA
Questo tutorial non vuole essere esaustivo ma ha l'intento di far conoscere meglio uno strumento, a mio avviso eccezionale, pressocché sconosciuto ai neofiti.
Il Filtro Avanzato è uno strumento assai piu' potente dell'AutoFilter che, spesso, è risolutivo in molti casi, in particolare quando si vogliono ottenere elenchi filtrati sulla base di determinate condizioni complesse come:
- elenca solo le righe con valore maggiore di ...
- elenca solo le righe che iniziano con ...
- elenca solo le righe che contengono ...
- elenca solo le righe inclusa in un intervallo di date
e così via.

La potenza del Filtro Avanzato può essere riassunta in due aspetti principali:
A. L'interfaccia incredibilmente semplice alla portata di qualsiasi grado di utenza
B. la possibilità di impostare condizioni complesse sulla stessa colonna, su più colonne contemporaneamente

E' importante sapere che il Filtro Avanzato può essere utilizzato con due approcci differenti:
  • impostandolo tramite l'apposita interfaccia di Excel: dal ribbon DATI -> Avanzate
  • impostandolo da codice VBA
Sul primo approccio si trovano infinità di guide sul web, quindi non sarà oggetto di questo tutorial.
Qui esamineremo il secondo approccio che, a mio avviso, è molto più flessibile.



TABELLE (NON SCHEDE!)
La sola condizione obbligatoria è la stessa richiesta dall'AutoFilter, ovvero i dati del foglio devono essere espressi in formato tabella (database), in cui:
- ogni colonna contiene il TIPO di dato
- ogni riga contiene i rispettivi VALORI
Ecco perché ho indicato NON SCHEDE!

Altra condizione indispensabile è che nei dati non siano presenti né celle unite, né righe o colonne nascoste.
Vi basta guardare il file di esempio per capire (vedere immagine 1)


Immagine 1



COME FUNZIONA IL FILTRO AVANZATO
Semplicissimo: andate sul foglio Ricerca e nella riga gialla digitate la lettera A nel campo CustomerID, poi premete il pulsante Filtra.
Vedrete che Excel vi elencherà i 4 clienti il cui CustomerID inizia con la A.
Adesso nel campo Contact Title digitate O e premete di nuovo Filtra.
Excel ora vi mostra solo 2 clienti.

Complimenti, avete appena utilizzato un filtro multi-colonna, senza sforzo. Facile vero?

Non importa se usate maiuscolo o minuscolo, è indifferente.


Immagine 2

Caratteri Jolly
Una cosa molto interessante è che potete perfezionare la ricerca sfuttando i caratteri jolly, gli stessi che utilizzate nel Trova e Sostituisci di Excel:
? = qualsiasi carattere singolo
* = qualsiasi numero di caratteri

Vedere anche l'articolo:

Alcuni esempi pratici:
- Digitate *X nel campo City, premete Filtra ed otterrete tutte le City in cui è contenuto il carattere X ed infatti Excel restituirà sia México che Bruxelles.
- Digitate *23 nel campo Address, premete Filtra ed otterrete gli indirizzi in è presente il numero 23.
- Digitate ?O nel campo CustomerID premete Filtra ed otterrete i clienti in cui la seconda lettera è O.

Potete anche combinare i due caratteri jolly insieme:
Dititate ???ES*R nel campo Contact Title ed otterrete i contatti in cui la 4ª e 5ª lettera sono ES e nelle restanti lettere è presente la R.
Aggiungete anche una L nel campo City e l'elenco si ridurrà ulteriormente filtrando le città che iniziano con L.

Premete il pulsante Azzera, per azzerare tutti i risultati.


Operatori di confronto
Questi operatori sono utilissimi, non solo con i numeri (che vedremo nella prossima parte 2) ma anche con le lettere.
Nel campo CustomerID digitate >S e premete Filtra.
Excel elencherà solo quelli che iniziano a partire dalla lettera S in avanti (S, T, V e W)
Se volete invertire la ricerca, basta sostituire >S con <S ed otterete l'elenco dalla A alla R.

Sfido chiunque a trovare una qualsiasi altra modalità per ottenere gli stessi risultati e con la stessa flessibilità.
Attenzione, perché siamo appena all'antipasto!!! SorrisoEOcchiali



COME IMPOSTARE IL FILTRO AVANZATO
Bene, ora che abbiamo assaggiato la potenza del Filtro Avanzato, descriverò come impostarlo.
Ai neofiti del VBA premetto subito di non spaventarsi: il tutto si riduce in pratica ad una sola riga di codice il che lo rende davvero semplice da usare.

Il file Excel che va utilizzato deve essere in formato XLSM, in cui la M finale sta per MACRO, cio' indica che il file consente l'uso di macro.
Quindi se il nostro file Excel è un normale XLSX la prima cosa da fare è aprirlo e salvarlo nel formato XLSM.

La sintassi per il Filtro Avanzato è la seguente:

<Range>.AdvancedFilter(Action, CriteriaRange, CopyToRange, Unique)

in cui <Range> è l'oggetto Range che rappresenta l'area da cui leggere i dati, ovvero il nostro database, rappresentato dal foglio Customers.
Nel nostro caso, essendoci meno di 100 righe ho impostato per comodità il range sul foglio Customers in questo modo:

Sheets("Customers").Range("A1:F100")

N.B. L'ideale sarebbe rendere dinamico il conteggio delle righe da usare. Approfondiremo questo aspetto nella PARTE 2.

Ora dobbiamo impostare gli altri parametri:
- Action:=xlFilterCopy - perché vogliamo copiare i dati nel foglio Ricerca
- CriteriaRange:=Sheets("Ricerca").Range("A1:F2") - qui ho definito l'area che contiene i criteri di ricerca
- CopyToRange:=Sheets("Ricerca").Range("A3:F3") - questa è la riga da cui voglio che i dati appaiano
- Unique:=False - così vedo tutti i risultati, anche quelli doppi (nel caso ve ne siano)


La riga completa di codice è:
Visual Basic:
    Sheets("Customers").Range("A1:F100").AdvancedFilter _
        Action:=xlFilterCopy, _
        CriteriaRange:=Sheets("Ricerca").Range("A1:F2"), _
        CopyToRange:=Sheets("Ricerca").Range("A3:F3"), _
        Unique:=False
Suggerirei di riflettere un attimo su questo:
Una singola riga di codice fornisce tutta la potenza che abbiamo visto prima.
Sembra incredibile!

Nel modulo modRicerca, incluso nel progetto allegato, trovate tutto il codice completo
Il resto del codice è 'solo di servizio' per rendere più pratico e semplice l'suo del filtro.
Il codice è ampiamente commentato, ma per chi vuole sbirciare senza aprire il progetto ecco il codice completo:

Visual Basic:
Option Explicit

Sub AzzeraFiltro()
    Sheets("Ricerca").Range("A2", "F2") = ""
    Sheets("Ricerca").Range("A4:F100") = "" ' svuoto i risultati di ricerca precedenti
    ' nascondo la seconda riga delle etichette
    'NascondiRigaEtichette
    Sheets("Ricerca").Range("A2").Select
End Sub

Sub ApplicaFiltro()
    Dim cell As Range
    Dim ZonaFiltro As Range  'l'area utilizzata come filtro
    Dim AvviaRicerca As Boolean

    ' controllo se ho indicato qualcosa da cercare nella ZonaFiltro,
    ' in caso contrario è inutile applicare un filtro
    Set ZonaFiltro = Sheets("Ricerca").Range("A2", "F2")
    For Each cell In ZonaFiltro
        If Len(cell) > 0 Then
            AvviaRicerca = True
            Exit For
        End If
    Next

    If AvviaRicerca = False Then
        Range("A4:F100") = "" ' svuoto i risultati di ricerca precedenti
        Exit Sub
    End If

    Sheets("Customers").Range("A1:F100").AdvancedFilter _
        Action:=xlFilterCopy, _
        CriteriaRange:=Sheets("Ricerca").Range("A1:F2"), _
        CopyToRange:=Sheets("Ricerca").Range("A3:F3"), _
        Unique:=False

    ' nascondo la seconda riga delle etichette
    'NascondiRigaEtichette
End Sub

Sub NascondiRigaEtichette()
    Sheets("Ricerca").Range("A3").EntireRow.Hidden = True
End Sub
Voglio far notare che relativa alla routine:
Visual Basic:
Sub NascondiRigaEtichette()
    Sheets("Ricerca").Range("A3").EntireRow.Hidden = True
End Sub
Al momento non viene utilizzata (le chiamate presenti sono commentate) perché volevo farvi capire tutti gli aspetti del Filtro Avanzato; ma se a voi piace potete usarla.
Perché accade ciò? Il motivo è semplice: il Filtro Avanzato 'raddoppia' le etichette delle intestazioni di campo, lo avrete già notato (nell'immagine 2).
Ora, magari a qualcuno da fastidio questo fatto, ecco allora la routine sopra che provvede a nascondere la seconda riga di etichette; per utilizzarla basta togliere il commento alle due chiamate di NascondiRigaEtichette.


CONCLUSIONE
In questa PARTE 1 del tutorial abbiamo appena assaggiato la potenza, semplicità e flessibilità del Filtro Avanzato di Excel che ritengo ineguagliate, se pensiamo che tutto ciò comporta una sola riga di codice.
Grazie all'uso dei caratteri Jolly e degli operatori di confronto la ricerca non è mai stata così pratica ed efficiente.

Nella PARTE 2 vedremo come spingerci oltre con funzionalità ancor più avanzate e potenti, ma sempre mantenendo la stessa semplicità.
 

Allegati

Ultima modifica:

Marius44

VBA Expert
Moderatore
Expert
9 Settembre 2015
5.515
63
75
Catania
Excel2010
129
Abbastanza conciso, esaustivo, comprensibile anche da un neofita.
Bellissimo lavoro. Complimenti. :applausi::stringomano:
Aspettiamo il seguito. Ciao,
Mario
 

alfrimpa

VBA Expert
Supermoderatore
Expert
18 Dicembre 2015
18.646
113
66
Napoli
2013
332
Grandissimo Giuliano complimenti vivissimi.

Mi rammarico assai di non avere ora un pc.

A quando la seconda parte?
 

alfrimpa

VBA Expert
Supermoderatore
Expert
18 Dicembre 2015
18.646
113
66
Napoli
2013
332
Essendo un tutorial la discussione viene chiusa.

Per richieste di chiarimenti o altro si aprano nuove discussioni nelle sezioni di Excel magari facendo riferimento a questa discussione.
 
Stato
Chiusa ad ulteriori risposte.

Sostieni ForumExcel

Aiutaci a sostenere le spese e a mantenere online la community attraverso una libera donazione!