Podkladová úloha: Pracujeme již podesáté se stejnou rozsáhlejší tabulkou. Stáhněte si sešit 2012-03 Filtry.xlsm z odkazu na konci článku. Sešit otevřete. Uvidíte zhruba toto:

Dostali jsme od vedení naléhavý úkol najít seznam všech měst, kde se vyskytuje nějaký Jan Hlaváček. Dozvěděli jsme se, že podobnou úlohu budeme dostávat pravidelně několikrát denně a s různými příjmeními a městy, zahájili jsme proto tvorbu makra.

Na list Param jsme si uložili kritéria pro potřebný rozšířený filtr:

Minule jsme nahráli makro, kde pomocí rozšířeného filtru nejprve vypíšeme dole pod databází výběr sloupců jen pro Příjmení, Jméno a Obec a to s výše uvedenými kritérii. Pak jsme zvolili rozšířený filtr s parametrem Kopírovat jinam s výše uvedenými kritérii, a navíc jsme požadovali jedinečné výskyty (tj. zaškrtli jsme pole Bez duplicitních záznamů).:

Většinu z nahraného kódu budeme muset nyní vyměnit. Cílový seznam budeme chtít setřídit abecedně, zkopírovat na samostatný list a doplnit o další podstatné informace, jako kterého jména a příjmení se týká a k jakému datu byl vytvořen. Potom po sobě musíme uklidit a smazat výstup pod databází. Řádky kódu:

Range("B1:C1,F1").Select
Range("F1").Activate
Selection.Copy

sloučíme do jediného příkazu

Range("B1:C1,F1").Copy

Důvod je jednoduchý. Pokud dokážeme ve VBA vyjádřit jednoznačně, co má zkopírovat do schránky, nic nás nenutí příslušné buňky selektovat (v Excelu to ve skutečnosti jinak než selektováním vyjádřit neumíme, proto to v nahraném makru je, jak je vidět výše).

Teď přijde zobecnění nahraného úseku, který zatím vypadá takto:

Range("A13689").Select
Selection.End(xlDown).Select
Selection.End(xlUp).Select
Range("A13688").Select
ActiveSheet.Paste

Uvědomme si cíl: Chceme najít pod databází volné místo, kam si můžeme dovolit zapsat výsledek rozšířeného filtru. Při nahrávání makra jsme se nejdříve dostali na konec databáze, pak jsme sjeli až na dno listu, a zase zpátky, až jsme nakonec zvolili buňku o 3 řádky pod databází. Ve VBA si můžeme počínat úsporněji (předpokládáme, že data zdaleka nedosahují přes 30000 řádků. Jinak by akce mohla být složitější):

Range("A65536"). End(xlUp).Offset(3).Select
ActiveSheet.Paste

První z výsledných řádků říká: „Začni na buňce A65536, (kde je u souborů typu „xls“ dno listu) vyjeď nahoru na nejbližší volnou buňku, a od ní se posuň o 3 řádky dolů. Tam postav selektor. Potom vypusť obsah schránky (kde máme uloženy hlavičky z předchozího kroku).

Další úsek nahraného programu zní:

Range("A1:I13685").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Range("Kr_prijmeni_Mesto"), CopyToRange:=Range("A13688:C13688"), Unique:= _
True

O něm jsme si minule naplánovali, že jej musíme zobecnit. Předně si všimneme argumentu CopyToRange, který představuje hlavičky zkopírované pod databázi. Ve chvíli, kdy jsme provedli před chvilkou „ActiveSheet.Paste“, byly tyto buňky selektovány. Mohli bychom tedy přiřadit CopyToRange:=Selection a mělo by to fungovat. Z důvodu větší stability proti výpadkům a i z důvodu přehlednějšího ladění však raději přiřadíme selektované buňky do proměnné typu Range, a tu poté budeme používat s větší jistotou. Kromě toho „Cil“ využijeme při následujícím třídění výsledku.

Dim Cil as Range
Set Cil = Selection

Druhé zobecnění se týká oblasti filtrování. Využijeme vlastnosti CurrentRegion, která vrátí k dané buňce „zaokrouhlení na obdélník“ tak, že pokud se dá po neprázdných hodnotách buněk přejít jako po mostě co nejvíc doprava, nebo co nejvíc dolů, rozšiřuje Excel „zaobdélníkování“, dokud nenarazí na hradbu prázdných buněk. A máme výsledek:

Range("A1 ").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Range("Kr_prijmeni_Mesto"), CopyToRange:=Cil, Unique:= True

Zbývá dopsat setřídění vzniklého bloku. Abychom si ušetřili starosti s vyťukáváním přesných znění argumentů, zase si tento kousek nahrajeme jako pomocné makro. List v našem souboru vypadá takto:

Spustíme nahrávání, přičemž jako první krok odselektujeme řádek 13688, a selektor postavíme např. na nadpis „obec“ v témž řádku. Pak provedeme setřídění a ukončíme makro. Vyjde nám:

Makro nám nahrálo kód obsahující objekt Sort, který byl zaveden od verze 2007, takže se teď musíme rozhodnout, zda uživatelé našeho sešitu nebudou náhodou vlastníky Excelu 2003 nebo staršího. S ohledem na velmi jednoduché požadavky na setřídění pouze podle jednoho klíče je tato struktura trochu „kanón na vrabce“, takže si raději dáme práci s nalezením parametrů staršího sortu, než abychom později snášeli stížnosti kolegů, že naše makro jim nefunguje. To si však nechme na příští pokračování.

Domácí úkol: Zkuste formulovat, do jakého nadřazeného objektu patří „Sort“, který nám nahrálo makro. (Nápověda: Pokud vám to není zřejmé z pohledu na nahraný kód, postavte se v nahrané subrutině na slovo „sort“ a stiskněte klávesu <F1>).

Aktuální verze příkladu ke stažení – 2012-03 Filtry.xlsm

Autor článku: Pavel Balek, info@abspektrum.cz, www.abspektrum.cz