|
Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:
Jak na AutoFilter ve VBA od vložení/odstranění, zjištění stavu, nastavení filtrovacích kritérií
Dopněno: 21.11.2014
Opět pro přehlednost je článek dělen na kapitoly tak ať si patřičný kód můžete najít rychleji:
Základní teorie k metodě AutoFilter:
AutoFilter(Field, Criteria1, Operator, Criteria2, VisibleDropDown)
Atribut | Poviné/Volitelné | Popis |
---|---|---|
Field | Volitelně | Číslo sloupce, na kterém se provede filtrování. Začíná se zleva od jedničky |
Criteria1 | Volitelně | Kriteria pro hledání (např. 100), doplněné zda se rovná (=) nebo je různé (<>), atd. Záleží i na hodnotě Operator (xlTop10Items) |
Operator | Volitelně | Konstanta specifikující typ filtru XlAutoFilterOperator, viz dále |
Criteria2 | Volitelně | Druhé kritérium |
VisibleDropDown | Volitelně | Hodnotu TRUE (výchozí) zobrazí šipku rozevíracího seznamu filtru. Hodnota FALSE skryje rozevírací šipku automatického filtru |
Hodnota | Konstanta | Popis |
---|---|---|
1 | xlAnd | Logical AND of Criteria1 and Criteria2 |
2 | xlOr | Logical OR of Criteria1 or Criteria2. |
3 | xlTop10Items | Highest-valued items displayed (number of items specified in Criteria1). |
4 | xlBottom10Items | Lowest-valued items displayed (number of items specified in Criteria1). |
5 | xlTop10Percent | Highest-valued items displayed (percentage specified in Criteria1). |
6 | xlBottom10Percent | Lowest-valued items displayed (percentage specified in Criteria1). |
7 | xlFilterValues | Filter values |
8 | xlFilterCellColor | Color of the cell |
9 | xlFilterFontColor | Color of the font |
10 | xlFilterIcon | Filter icon |
11 | xlFilterDynamic | Dynamic filter |
Například věk větší nebo roven 18 ...
Criteria1:=">=18"
Chcete-li zobrazit prázdné buňky
Criteria1:="="
Případně pro zobrazení neprázdných buněk
Criteria1:="<>"
Pro zobrazení textu začínající na P, podrobněji se zmiňuji v kapitole o filtrování textu.
Criteria1:="=P*"
Buňky nesmí obsahovat x ;)
Criteria1:="<>*x*"
Jak zapisovat hledaná kritéria:
Criteria1:="=100"
Criteria1:=">=100"
Criteria1:="<>*AB*"
Criteria1:=RGB(255, 255, 0)
Criteria1:=Array("Modrá", "Zelená", "Žlutá")
Symboly
Pokud hledáte hvězdičku v textu je nutno použít znak vlnovka.
Criteria1:="*~**"
Pro naše testy budeme používat následující zdrojovou tabulku:
Jméno | Stát | Počet | Id | Datum | Čas | Barva |
---|---|---|---|---|---|---|
Fantomas | CZ | 12 | 1 | 29.6.2014 | 10:10 | modrá |
Fantomas | EN | 14 | 2 | 30.6.2014 | 10:25 | zelená |
Fantomas | CZ | 16 | 3 | 1.7.2014 | 10:40 | červená |
Jak pomocí VBA vkládat a odstraňovat automatické filtry
Pokud záhlaví tabulky začíná v buňce A1
ActiveSheet.Range("A1").AutoFilter
Pokud záhlaví tabulky nezačíná v buňce A1 nebo filtr nechcete aplikovat na celou tabulku:
ActiveSheet.Range("A1:D1").AutoFilter
Pokud by již byl aplikován filtr tak ať jej neodstraníte, v další kapitole se dozvíte jak zjistit, za v listě je již aplikován filtr ;)
With ActiveSheet
.AutoFilterMode = False
.Range("A1:D1").AutoFilter
End With
Pokud tabulka obsahuje filtr
Worksheets("List1").AutoFilterMode = False
Jeden z možných postupu, jak zjistit, zda tabulka obsahuje filtr a případně zda jsou data filtrovaná. Využijeme
With ActiveSheet
If .AutoFilterMode = True And .FilterMode = True Then
MsgBox "Data jsou filtrovaná. Sešit obsahuje filtr ;)"
ElseIf .AutoFilterMode = True Then
MsgBox "K dispozici filtry - data nefiltrovaná."
Else
MsgBox "Sešit neobsahuje filtry."
End If
End With
Jak vyfiltrovat například číslo 12
Range("A1:D1").AutoFilter Field:=3, Criteria1:=12
Jak vyfiltrovat například číslo 12 a 14
Range("A1:D1").AutoFilter Field:=3, Criteria1:=12, Operator:=xlOr, Criteria2:=14
Jak vyfiltrovat například čísla větší než 12
Range("A1:D1").AutoFilter Field:=3, Criteria1:=">12"
V této kapitole jsou ukázky filtru buněk s textem. Jak je všeobecně známo tak následující znaky mají tento význam:
Vybere jména začinající na F
Range("B5").AutoFilter Field:=1, Criteria1:="F*"
Vybere slova která začínají na "F" a dále je jakýkoli znak a pak pokračuje "ntomas". (tj. Vybere Fantomas, Fontomas slovo Foontomas, již nevybere má dvě oo)
Range("B5").AutoFilter Field:=1, Criteria1:="F?ntomas"
Vybere slova která začinají na "F" a dále je jakýkoliv počet znaků a slovo a pak pokračuje/končí "ntomas". (tj. Vybere Foontomas, Fontomas)
Range("B5").AutoFilter Field:=1, Criteria1:="F*ntomas"
Nezobrazí slova obsahující "x"
Range("B5").AutoFilter Field:=1, Criteria1:="<>*x*"
Zobrazí slova, která obsahující "x"
Range("B5").AutoFilter Field:=1, Criteria1:="=*x*"
Zobrazí slova Pat a Mat
Range("B5").AutoFilter Field:=1, Criteria1:="Pat", Operator:=xlOr, Criteria2:="Mat"
Pro volbu datum využijeme funkci DateSerial.
DateSerial(year, month, day)
Vlastní kód:
Dim Date1 As Date
Dim Date_str As Long
' datum můžete načíst z buňky (buněk)
Date1 = DateSerial(2014, 7, 7)
Date_str = Date1
' předpkládám, že už je na listu filtr
Range("B4").AutoFilter Field:=1, Criteria1:=">" & Date_str
Kód můžete doplnit o druhé kritérium, atd:
Dim Date1 As Date
Dim Date2 As Date
Dim Date_str1 As Long
Dim Date_str2 As Long
Date1 = DateSerial(2014, 7, 7)
Date_str1 = Date1
Date2 = DateSerial(2014, 7, 16)
Date_str2 = Date2
' předpkládám, že už je na listu filtr
Range("B4").AutoFilter Field:=1, Criteria1:=">" & Date_str1, _
Operator:=xlAnd, Criteria2:="<" & Date_str2
Zpět na seznam kapitol o tisku ve Wordu.
Vyfiltrovat čas větší než 12:10
Range("B5").AutoFilter Field:=1, Criteria1:=">12:10"
Vyfiltrovat čas větší než 12:10 využitím funkce TimeSerial
Dim db_Date As Double
db_Date = TimeSerial(12, 10, 0)
Range("B5").AutoFilter
Range("B5").AutoFilter Field:=1, Criteria1:=">" & Format(db_Date, "hh:mm")
Čas využitím nastavení formátu
Dim Date1 As Long
Range("B5").AutoFilter Field:=1, Criteria1:=">" & Format(0.506944444444444, "hh:mm")
Další v přípravě.
Pokud víte číslo ve formátu RGB tak lmůžete využít:
Range("B3").AutoFilter Field:=1, Criteria1:=RGB(225, 0, 0), Operator:=xlFilterCellColor
Pro dvě kritéria
Range("A1:D1").AutoFilter Field:=3, Criteria1:=12, _
Operator:=xlOr, Criteria2:=14
Pro několik kriterií:
ActiveSheet.Range("$A$1:$D$23").AutoFilter Field:=3, _
Criteria1:=Array("14", "16", "17"), _
Operator:=xlFilterValues)
Pro rozsah kritérií:
ActiveSheet.Range("$A$1:$D$23").AutoFilter Field:=3, _
Criteria1:="<22", _
Operator:=xlAnd, Criteria2:=">14"
Podrobnější popis a další ukázky v přípravě...
K dispozici několik ukázkových sešitů:
Soubor
Autofilter - praktické příklady - Excel VBA
ke stažení zdarma. Soubor využívá makra (pro Excel 2007 a novější).
Soubor
Jak filtrovat jména pomocí VBA Excel
ke stažení zdarma. Soubor využívá makra.
Soubor
Jak filtrovat datum pomocí VBA Excel
ke stažení zdarma. Soubor využívá makra.
Pokud už máte buňku (oblast) označenou můžete s ními dále pracovat například
Využíváte na filtry v Excel VBA? Máte nějaké zajímavé kódy, o které se chcete podělit? Můžete je zmínit v komentářích.
Článek byl aktualizován: 19.09.2020 11:07
Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.
Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.
Přidáno: 22.07.14 12:47
... Ahoj/Dobry den - skusal som si automaticky filter a mam otazku reps priklad: - chcem na zakalde neakych kriterii urobit filter a v x- tom stlpci spocitat hodnoty napr. sum() - skusal som spocita mi aj skryte hdontoy , da sa to neako obist ? PS: neda sa tu pridat mail ench mi pride mail ked bude odpoved na moj prispevok ( pre buducnost ) Ďakujem
Přidáno: 24.07.14 15:11
To Marianus: Využít funcki SUBTOTAL
Bohužel redakční systém na kterém web běží tohle neumí. V plánu mám jen doprogramovat možnost objednat si placenou konzultaci. Zatím, ale řeším jiné záležitosti.
Přidáno: 09.09.14 16:37
Dobrý den, k filtru na datum bych možná doplnil zjednodušení ve tvaru:
Range("B4").AutoFilter Field:=1, Criteria1:=">" & CLng(CDate("7.7.2014"))
Přidáno: 28.01.16 12:00
Dobrý den, ráda bych se zeptala, je-li nějaká možnost, jak filtrovat data současně ve více sloupcích najednou (stejně pojmenovaných sloupcích, filtr dle stejných kritérií). Příklad: Máme sloupce rok, prijem, vydej, součet rok, prijem, vydej,... ráda bych vyfiltrovala všechny prijmy (bez ohledu na rok).. Nebo máme měřená data pro jednu jednotku (rychlost, výkon, ztráty apod) a chceme filtrovat vždy výkony pro jednotlivé jednotky.. Díky za rady..
Přidáno: 06.07.16 09:54
Dobrý den, používám excel 2013 a řeším pomocí VBA filtrování datumů. Bohužel filtr se chová tak, že skryje všechny řádky, včetně těch, které by měl zobrazit. Pokud se pak podívám na listu na zadaná kritéria filtru, tak ty jsou tam vyplněna správně a po jejich potvrzení na OK, filtr zobrazí data, tak jak má. Chová se to naprosto stejně i s daty a s kódem na této výukové stránce. Předpokládám, že tedy nemám chybu v kódu. Netušíte, kde by mohla být chyba a jak z toho ven? Pan google mi zatím nedokázal poradit. Díky.
Přidáno: 06.07.16 18:24
Tak nakonec můj problém vyřešilo použití zde uvedeného kódu od LamRis. Díky!
Přidáno: 18.09.16 05:39
Dobrý den, potrebuji poradit makro se skladovou tabulkou. Makro na vkladani dat mam hotove, ale nejde mi na vyskladeni polozky .... Dejme tomu ze v liste1 do policek B2:B5 vlozim data ty se ulozi do listu2 A1:A4.. a ted problem : potrebuji makro, ktere podle stejnych vstupnich parametru polozku najde ( vyfiltruje) a smaze ji... Dekuji za pomoc
Přidáno: 21.09.16 12:24
Dobrý den, zkouším jednoduché makýrko: ActiveSheet.Range("$A$8:$D$897").AutoFilter Field:=4, Criteria1:="=> (" 'List1'"!K4")" _ , Operator:=xlAnd, Criteria2:="<=49000", které nefunguje = potřebuji, aby za Criteria1:="=> mohlo být číslo z buňky K4 list1. Tzn, že bych mohl v K4 zadávat různé hodnoty a pak spusti makro= filtr, který zobrazil hodnoty mezi zadanými čísly.
Přidáno: 30.09.16 18:12
wow, předem děkuji za výborný obsah webu. Nějakou chvíli jsem se trápil s automatickým filtrem na datum, který i s postupem od pana Lasáka nevracel nic (ač by měl) mám konkrétně na mysli: Dim Date1 As Date Dim Date_str As Long ' datum můžete načíst z buňky (buněk) Date1 = DateSerial(2014, 7, 7) Date_str = Date1 ' předpkládám, že už je na listu filtr Range("B4").AutoFilter Field:=1, Criteria1:=">" & Date_str filtr měl parametry doplněny na první pohled v pořádku, ale na pozadí nepracoval s atributy jako s daty (možná by něbylo od věci kdyby byla kapitola doplněna ;-). Nicméně zkusil jsem jako již kolega přede mnou postup od LamRis a světe div se, filtr funguje v naprostém pořádku, díky moc.
Pomohl Vám návod? Sdílejte na Facebooku, G+ |
||
LinkedIn... |
Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2021 |