Tweet

Filtry - AutoFilter v Excel VBA

Úvodem do filtru ve VBA

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:

• Teorie k metodě AutoFilter
  • syntaxe
  • XlAutoFilterOperator
  • jak hledat, <,>,*,?,~
  • array
  • barva
• Zdrojová tabulka
• Vkládaní / odstranění filtru
• Kontrola stavu filtru obsahuje list, kolik je záznamů, ...
• AutoFilter pro:
  • Filtry na čísla
  • Filtry na text (včetně * ? konvence)
  • Filtry na datum den, od, do, mezi, ...
  • Filtry na čas přesný čas, rozmezí
  • Filtry na barvu
• Pokročilé možnosti filtrů - dva filtry, filtry pro různé sloupce
• Ke stažení - ukázkové sešity s využitím AutoFilter:
  • AutoFilter - souhrný sešit s příklady
  • AutoFilter - Pro práci s texty
  • AutoFilter - Filtr s datum
• Další související články

Teorie AutoFilter

Základní teorie k metodě AutoFilter:

Syntaxe

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 fil­tru

XlAutoFilterOperator

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

Criteria

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 hledat, <,>,*,?,~

Jak zapisovat hledaná kritéria:

Criteria1:="=100" Criteria1:=">=100" Criteria1:="<>*AB*" Criteria1:=RGB(255, 255, 0) Criteria1:=Array("Modrá", "Zelená", "Žlutá")

Symboly

• * Hvězdička - nahrazuje jakýkoli počet znaku
• ? Otazník - nahrazuje právě jeden znak
• ~ Vlnovka

Tipy Triky

Pokud hledáte hvězdičku v textu je nutno použít znak vlnovka.

Criteria1:="*~**"

Zpět na seznam kapitol.

Zdrojová tabulka

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á

Zpět na seznam kapitol.

Vkládaní / odstranění filtru

Jak pomocí VBA vkládat a odstraňovat automatické filtry

Vložení filtru

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

Odstranění filtru

Pokud tabulka obsahuje filtr

Worksheets("List1").AutoFilterMode = False

Zpět na seznam kapitol.

Kontrola stavu filtru

Jeden z možných postupu, jak zjistit, zda tabulka obsahuje filtr a případně zda jsou data filtrovaná. Využijeme

• .AutoFilterMode
• .FilterMode

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

Zpět na seznam kapitol.

Filtry na čísla

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"

Zpět na seznam kapitol.

Filtry na text

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:

• * - hvězdička - nahrazuje 0 až "nekonečně" znaků
• ? - otazník - nahrazuje právě jeden znak

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"

Zpět na seznam kapitol.

Filtry na datum

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.

Filtry na čas

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ě.

Zpět na seznam kapitol.

Filtry na barvu

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

Zpět na seznam kapitol.

Pokročilé možnosti filtrů

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ě...

Zpět na seznam kapitol.

Ke stažení

K dispozici několik ukázkových sešitů:

• AutoFilter - souhrný sešit s příklady
• AutoFilter - Pro práci s texty
• AutoFilter - Filtr s datum

AutoFilter - souhrný sešit s příklady

Soubor Autofilter - praktické příklady - Excel VBA ke stažení zdarma. Soubor využívá makra (pro Excel 2007 a novější).

AutoFilter - Pro práci s texty

Soubor Jak filtrovat jména pomocí VBA Excel ke stažení zdarma. Soubor využívá makra.

AutoFilter - Filtr s datum

Soubor Jak filtrovat datum pomocí VBA Excel ke stažení zdarma. Soubor využívá makra.

Další související články:

Pokud už máte buňku (oblast) označenou můžete s ními dále pracovat například

• AdvancedFilter metoda - Excel VBA
• Filtry - Microsoft Excel 2010 - bez VBA
• Rozšířený filtr - Excel - bez VBA

Závěrem

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

Pavel Lasák - autor webu

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ů.

           

Komentáře