VBA - Filtrare Data Validation List

In aceasta vara am lucrat la un proiect in excel care mi-a testat capacitatile si datorita caruia am pornit pe calea programarii VBA. Dupa mai multe postari care au avut ca subiect diverse formule, cred ca acum este momentul potrivit pentru a va impartasi si cateva exemple cu VBA.

Am hotarat ca prima postare pe acest subiect sa fie una usoara si anume filtrarea listei create cu Data Validation. Trebuie sa va marturisesc ca eu folosesc Data Validation List in foarte multe fisiere, in general este foarta utila in cadrul formularelor care sunt folosite de catre alte persoane. Modalitatea de folosire a listei create cu Data Validation este de multe ori ingreunata de marimea mare a listei care dorim sa o afisam.

Pentru a rezolva aceasta problema putem sa reorganizam informatiile pe mai multe categorii si/sau subcategorii care sa ne ajuta sa realizam o filtrare initiala (Dependent Data Validation List). Dar nu in toate cazurile informatiile pot fi reorganizate in acest fel si pentru aceasta lucru eu am descoperit filtrarea listei cu ajutorul unui macro.

Pentru exemplul de azi am ales sa folosim o lista de clienti pe baza careia doresc sa fac o lista cu Data Validation care sa fie folosita intr-un formular (de exemplu intr-o solicitare de facturare). Pentru acest fisier avem nevoie de doua sheet-uri: Lista clienti si Formular.

Sheet-ul  Lista clienti  este un sheet ajutator in care avem lista de clienti pentru formular. Tot in acest sheet se va filtra lista de clienti pe baza a ceea ce este completat in formular.

Pe coloana A:A am completat lista de clienti a companiei. In celula C2 se va aduce din sheet-ul Formular filtrarea pe care o doreste utilizatorul. Formula care am folosit-o in celula C2 este urmtoarea: =IF(formular!B2="","",formular!B2). Iar in coloana D se va filtra cu ajutorul macro-ului lista de clienti de pe coloana A:A.









In sheet-ul Formular am doua campuri: filtrare clienti si client. In campul Filtrare Client se va completa de catre utilizator primele litere din numele clientului sau prima litera sau cum doreste ele filtrarea. In campul Client se va alege din lista derulanta clientul pentru care se completeaza formularul. In plus am adaugat si o poza pentru a accesa macro-ul pentru filtrarea listei de clienti.

In continuare voi prezenta macro-ul folosit pentru filtrarea listei de clienti:

Public Sub FiltrareClienti()

Dim wsSD As Worksheet
Set wsSD = Sheets("Lista clienti")

wsSD.Range("D1").EntireColumn.ClearContents

      With wsSD
        .Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Range( _
        "C1:C2"), CopyToRange:=.Range("D1"), Unique:=True
      End With
     
End Sub

Folosim wsSD.Range("D1").EntireColumn.ClearContents pentru a sterge eventualele date din coloana D:D din sheet-ul Lista clienti. Realizam aceasta stergere a datelor pentru a ne asigura ca noua filtrare a listei nu va contine date din filtrarea anterioara.

Pentru filtrarea propriu zisa se foloseste varianta VBA a Advance filter. Aceasta comanda permite utilizatorilor filtrarea unui tabel in functie de una sau mai multe conditii. Eu folosesc de cele mai multe ori advance filter pentru a genera dintr-o lista, aceeasi lista dar fara campuri duplicate.

      With wsSD
        .Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Range( _
        "C1:C2"), CopyToRange:=.Range("D1"), Unique:=True
      End With

 In VBA advance filter are urmatoarea sintaxa:  .AdvancedFilter(Action, CriteriaRange, CopyToRange, Unique)

• Action - reprezinta actiunea pe care dorim sa o realizeze Advanced filter si poate fi una din urmatoarele optiuni: xlFilterCopy si xlFilterInPlace. Cu prima copiem datele rezultate in alta parte a fisierului si cu al doilea se va face filtrare direct in tabel.
• CriteriaRange - reprezinta criteria pe baza careia se face filtrarea. Aici se va alege din cadrul fisierului celulele care contin regula de filtrare. In cazul nostru aceste celule sunt reprezentate de celule C1:C2 din sheet-ul Lista clienti. CriteriaRange este optionala, iar daca argumentul este omis nu va exista nici o regula de filtrare.
• CopyToRange - reprezinta argumentul folosit in cazul in care am ales ca lista filtrata sa fie copiata in alta parte din fisier. La fel ca la CriteriaRange aici vom scrie celula sau celulele de unde se incepe copierea tabelului filtrat. In cazul nostru aceasta celula este D1 din sheet-ul Lista clienti.
• Unique - Este un argument logic si se va alege True daca se doreste filtrarea pe inregistrari unice din tabel si False pentru a filtra toate inregistrarile. Valoarea implicita este Fals. 

Am declarat procedura pentru filtrarea listei de clienti publica pentru a o putea atribui pozei pe care am importat-o in fisier.

Pentru a termina de implementat aceasta optiuni in fisier mai trebuie sa cream lista din campul Client cu ajutorul Data Validation List. Pentru acest lucru alegem celula B3 din sheet-ul Formular, mergem in meniu la DATA si alegem Data Validition si apoi list. Aici vom folosi urmatoarea formula: =IF($B$2="",Lista_clienti,ListaFiltrata).

Daca in camplul in care scriem regula de filtrare nu este scris nimic se va afisia toata lista de clienti (Lista_clienti am declarata cu Named Ranges si am creat-o dinamic), iar daca s-a completat o regula de filtrare se va afisa lista filtrata (creata la fel cu Named Ranges si neaparat trebuie creata dinamic).

Pentru a intelege mai usor cum se realizeaza cu ajutorul VBA filtrarea Data Validation List puteti downloada fisierul cu exemplul de la urmatoarea link: VBA-filtrare clienti.xlsm