V předchozím dílu jsme demonstrovali možnost „Kopírovat jinam“, ale pro velký rozsah jsme ponechali úpravy nahraného kódu, jeho optimalizaci a úklid na listu do dílu dnešního.
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ší…
