Excel - Makro pro skrytí prázdných sloupců a opětovné zobrazení neprázdných sloupců při změně filtru

Predpokladám, že tých stĺpcov, ktoré môžu mať (i naraz) prázdne obsahy pri určitých filtroch, je viac a môžu sa vyskytnúť v nesúvislej oblasti na viacerých miestach tabuľky (napr pri danom filtri budú prázdne stĺpce D, H, K ...) a chceme skryť všetky prázdne stĺpce...
Riešenie: udalosť zmenu filtrácie zachytiť nejakým ľubovoľným pomocným vzorcom, ktorý bude filtráciou meniť svoju hodnotu a makro spúšťať udalosťou Worksheet_Calculate (v module príslušného hárka!). Táto udalosť nastáva vždy pri prepočítavaní hárka (pokiaľ nie je automatické prepočítavanie vypnuté)
Čiže máme podchytenú reakciu na zmenu filtra.
Následne je potrebné vytvoriť kód, ktorý najlepšie funkciou SUBTOTAL (ktorý rešpektuje skutočnosť skrývania riadkov filtrovaním) s parametrom funkcie 103 (t.j. COUNTA), ktorý spočítava počet zobrazených údajov (či už číselných alebo reťazcových) v neskrytých bunkách stĺpca. Funkciu je možné zapracovať do kódu ako Worksheetfunction (t.j vzorce SUBTOTAL jednotlivých stĺpcov nemusia byť v samotnej tabuľke). Prejdením všetkých stĺpcov v cykle sa zistia stĺpce, v ktorých je hodnota SUBTOTAL (103,...) rovná 1 (t.j. obsahuje len záhlavie) a každý takýto stĺpec sa nechá skryť.
Ako odkryť tie stĺpce, ktoré zmenou filtra menia prázdny stav na neprázdny, keďže doposiaľ sú skryté? Tak, že každou zmenou filtra sa najprv všetky stĺpce odkryjú aby sa mohla previesť kontrola SUBTOTAL-ov a následne sa skryjú len tie stĺpce, pri ktorých nastala zmena filtrovaním na stav bez hodnoty.
Všetky skrývania a odkrývania prebehnú utajene (t.j. bez preblikávania obrazovky) tak, že sa na začiatku dočasne vypne aktualizácia obrazovky príkazom Application.Screenupdating=False

Údajně by to mělo fungovat pomocí tohoto kódu

Private Sub Worksheet_Calculate()
    Dim tbl As ListObject
    Dim col As ListColumn
    Set tbl = Me.ListObjects("example")
    For Each col In tbl.ListColumns
        col.Range.EntireColumn.Hidden = (Application.Subtotal(103, col.DataBodyRange) = 0)
    Next col
End Sub

Jenže, člověk na podpoře microsoft mně nasdílel soubor s tímto kódem, kde to funguje. Tam jsem napsal naprosto totožný soubor se stejným, kde to nefunguje. Očividně mám tam něco špatně, ale není mně jasné co.

Oba soubory jsou v příloze.

Prosím o radu,

Děkuji

Toto je odborná verzia a neviem, prečo to nejde, možno verziou Excelu, zastavuje mi to na riadku Me.ListObjects("example") ...
Každopádne Ti tam chýbali dve veci:
úplne za prvé: nemá Ti tam čo spúšťať procedúru, pretože tam nemáš funkciu =NOW() (tú si zvolil on v bunke G1, môže však byť akákoľvek iná, ktorá spôsobí prepočet), ktorá zaručuje prepočítavanie hárka a týmpádom spúšťanie udalosti Calculate
za druhé: on tam má použitú pomenovanú oblasť A2:C5 (nazval ju "example" - zhodou okolností totožne ako názov hárka ale to je niečo úplne iné). Pozri si na karte "Vzorce - správca názvov"
a možno...za tretie: správca názvov sa správa inak keď je tabuľka bežná, než keď je tabuľka formátované ako "Tabuľka" (zistíš, keď si v oboch prípadoch skusmo nadefinuješ oba druhy...
Každopádne, ako vravím, súbor "working" je na mňa spôsob príliš odborného zápisu a tak ja si tieto veci riešim po svojom. Tu je môj kód, ktorý pracuje tak, ako som popísal v predošlom mojom poste (zvolil som si 5 stĺpcovú oblasť, aby sme videli, že skrýva aj nesúvislé polia stĺpcov:
Najprv odkryjem 5 stĺpcov, čím si zaručím, že sa hneď na začiatku všetko odkryje a pripraví pre podmienky novej filtrácie.
Prejdem v cykle 5 stĺpcov a v každom funciou Subtotal 103 kontrolujem, či je výsledkom 1. Jednotka totiž znamená, že stĺpec obsahuje len 1 záznam = hlavičku.
Ak je výsledkom 1, tak stĺpec skryjem.
Hotovo

Private Sub Worksheet_Calculate()
Columns("A:E").Hidden = False
For i = 1 To 5
    If Application.Subtotal(103, Columns(i)) = 1 Then Columns(i).Hidden = True
Next i
End Sub

Toto je odborná verzia a neviem, prečo to nejde, možno verziou Excelu, zastavuje mi to na riadku Me.ListObjects("example") ...

Kód se zastaví na řádku Me.ListObjects("example"), protože žádný takový objekt v listu neexistuje. Michal nepoužil objekt "Tabulka"

za druhé: on tam má použitú pomenovanú oblasť A2:C5 (nazval ju "example" - zhodou okolností totožne ako názov hárka ale to je niečo úplne iné). Pozri si na karte "Vzorce - správca názvov"

Zde se nejedná o pojmenovanou oblast, ale o jméno Tabulky. Pokud ve svém excelu vytvořím objekt Tabulka dostane jméno Tabulka1, které se automaticky objeví ve správci názvů.

Ahoj, laboroval som s tým, tabuľku som previedol na objekt "Tabuľka", mal som v správcovi názvov objekt rovnaký ako v pracujúcom súbore, s menom example, napriek tomu mi to nešlo. S tým pomenovaním oblasti som sa asi zle vyjadril, lebo som tým myslel pomenovaný objekt v správcovi názvov, nie oblasť buniek, ktorú by som si pomenoval vľavo od riadka vzorcov... (preto som ho odkázal na správcu názvov, aby videl rozdiel medzi svojim a funkčným súborom). Píšem z mobilu, preto si priložený súbor teraz neviem prezrieť a podľa toho sa ho pokúsiť ešte raz nasimulovať, ale nepochybujem, že to máš správne.
Ja som objekt tabuľka premenoval podľa vzorového súboru na "example", nenapadlo ma skúsiť to naopak, t.j. nechať ho tak, ako si ho pomenoval Excel a naopak- zmeniť jeho názov v kóde... Možno tam niekde som spravil chybu...
P.S. ja som sa zatiaľ nedonútil deklarovať premenné tohto druhu...moja chyba, ale mám radšej typy riešení ako som to predložil vo svojej verzii. Je to pre mňa jednoduchšie. Nepoviem, keby sa v kóde často opakovali dané premenné, má to svoj význam, no napriek tomu by som uprednostnil svoj vžitý postup alebo zjednodušenie inou formou pomocou With a pod

To, že je funkce now() pouze k aktivaci makra, jsem vůbec netušil.

Každopádně, tento kód už funguje přesně jak má. A navíc, skryje i ten sloupec, který tu funkci now() obsahuje, což je perfektní.

Mockrát díky

Bohužel musím zpětně otevřít toto téma, protože jsem až dnes přišel na problém Vašeho kódu. Pokud ho otevřou dva lidi, začne to nějak ustavičně "problikávat". Bohužel nemám žádnou nahrávku ani printscreen, ale očividně je to tím, když ho otevřu v Office 365, kde se to automaticky aktualizuje, což očividně utáhne pouze u jednoho uživatele.

Neznáte, prosím Vás, nějaké řešení?

Děkuji

Jak už psal Robert, chybí Ti tam dvě věci.
1. Objekt Tabulka, tedy ListObject se kterým kód pracuje
2. Někde v listu funkce NYNÍ(), v EN verzi NOW(), která způsobí přepočet listu při manipulaci s filtrem. Přepočet listu spustí kód.

Soubor:
tabulka převedena na Tabulku se stylem "žádný", automaticky generované jméno tabulky - Tabulka1 změněno v kódu > Me.ListObjects("Tabulka1")

No, to je ten problém Microsoft, zprznit Excel tím, že se Makra budou psát v jeho "úžasném" jazyce. Každopádně, tohle je už něco pokročilejší, ale ze zkušeností vím, že makra bych si musel psát i na úplné banality (např. barva ohraničení označených buněk)

Každopádně, to už odbočuji od tématu. Kód, co napsal robert13, je za mě lepší, jelikož skryje i ten sloupec s funkcí =now()

I přesto díky

Bohužel beru zpět, kód co uvedl robert13, nějak nemá rád skupinovou práci na jednom souboru.

Každopádně, asi budu za blbce, ale já stále nechápu, jak jste docílil, aby Vám ten kód fungoval. Tabulku jsem nějak neupravoval ve stylu.

Je potrebné si uvedomiť, že KAŽDOU zmenou v hárku dochádza prostredníctvom funkcie NOW() k spusteniu makra a to znamená, že sa odkryjú všetky stĺpce, v cykle sa vyhodnotia obsahy jednotlivých stĺpcov a skryjú stĺpce bez obsahu. To znamená, že aj vtedy, keď je to úplne zbytočné, napr aj doplnením ďalšieho údaja do stĺpca, kde už údaje sú. Vzhľadom na rýchlosť to nevadí, pokiaľ pracuje so súborom jeden užívateľ, pretože sa popri jeho práci nemá dôvod kód spúšťať mimo spustenia ním samotným. Ono keby sa beh spomalil, bolo by vidieť každý jednotlivý krok, ako sa na okamih poodkrývajú všetky skryté stĺpce a ako sa postupne skrývajú "nulové". Existuje príkaz, ktorým sa aktualizácia obrazovky zakáže, kým nie je vykonaný celý kód. Už som o ňom písal, ale zabudol som ho zapísať do kódu. Pod názov procedúry vlož nový riadok s príkazom: Application.ScreenUpdating=False a pred End Sub príkaz Application.ScreenUpdating=True. Toto spôsobí, že obrazovka sa obnoví až bude kód kompletne vykonaný a teda akékoľvek preblikávanie jednotlivých krokov kódu je potlačené, zobrazí sa až výsledný stav.

Ďalšou možnosťou je namiesto NOW() vložiť inú funkciu, ktorá sa nebude prepočítavať KAŽDÝM úkonom. Napr do našej G1 tiež použiť už spomenutú funkciu SUBTOTAL(103, A:A), alebo konkrétnejšie s rozsahom riadkov, v ktorom sa v stĺpci A reálne môžu nachádzať údaje napr SUBTOTAL(103, A1:A1000). Čo získame? Ak práve nebudeme manipulovať s filtrom, kód sa nebude spúšťať, kým sa do stĺpca A nezapíše ďalší údaj. Žiadne iné úkony kód spúšťať NEBUDÚ. Čo je teda určite OBROVSKÉ odľahčenie od zbytočného priebehu kódu po každučkom ľubovoľnom úkone v hárku, napriek tomu, že kód zbytočne prebehne pri vložení nového údaja do stĺpca A.
Samozrejme, najlepšie je použiť zákaz aktualizácie obrazovky a zároveň vyššie popísaným spôsobom zmeniť funkciu NOW().

Díky, to SUBTOTAL mělo skutečně vliv. Nenapadlo mě, že funkce now bude tak namáhavá. Navíc, první sloupec obsahuje jenom čísla, takže nebyl problém s tím, co vybrat.

A mám to už otestované, teď už to funguje bez problému. Tím bych to uzavřel. Ještě jednou děkuji.

Tak bohužel musím opětovně otevřít toto téma. Navýšil jsem rozsah, aby to při přidávání nových řádků nemusel měnit. A tím vznikl problém, když zvolím u jakéhokoliv filtr "blank"

V takovém případě musím odkrýt všech slunce a kompletně zrušit filtr. Druhý sloupec zůstal zobrazený jenom proto, že se na posledním řádku něco nachází.

Lze to opravit, prosím?

Děkuji

No veď ale taká bola požiadavka: stĺpec, ktorý neobsahuje žiadne hodnoty (čo samozrejme nastane i pri voľbe "blank" pre daný stĺpec) má byť skrytý.
Z toho vyplýva, že voľba "blank" je neprípustná, pretože stĺpec s takto aktivovaným filtrom ostane trvale skrytý a spôsobí skrytie i iného stĺpca, ktorý sa takouto voľbou stane tiež prázdnym.
Jedná sa o vzájomne sa vylučujúce požiadavky.

Ano, jsou to rozdílné požadavky. Ale já jsem chtěl skrýt pouze skrýt sloupce, které při daném vyfiltrování nic neobsahují.

Nicméně, v tomhle případě by nebylo nutné měnit makro, ale pouze přidat další, které by zakázalo ve filtru možnost vybrat (Blanks). Tím by se celý problém vyřešil.

Jak to, prosím Vás, napíšu? (Jestli je to teda možné, ale pochybuji že ne, když jsou v programování tyhle věci zcela normální)

Děkuji

Pozriem sa na to, ako by sme to poriešili, ale nie je to možné hneď, takže pokiaľ Ti niekto iný neposkytne riešenie skôr, tak strpenie...

No tak aby sme to nepredlžovali:

Zaveď si v hárku tlačidlo (mimo oblasti, ktorá sa môže skrývať) a priraď mu nasledujúce makro, ktoré umiestniš do všeobecného modulu

Sub AnulaciaFiltra()
With ActiveSheet
If .AutoFilterMode = True And .FilterMode = True Then
.Range("A1").AutoFilter
.Range("A1").AutoFilter
End If
End With
End Sub

Keď si zvolíš nešťastný filter, týmto tlačidlom si ušetríš manuálnu nápravu. Filter sa vypne a opätovne zapne do východzieho stavu.
Z princípu nemôže fungovať to, aby zostal viditeľný stĺpec, ktorý nič neobsahuje, keď zadaním bolo to, že takýto stĺpec má byť skrytý.
Z požiadavky vyplýva i to, že ak vplyvom filtra "prázdne" v určitom jednom stĺpci sa stanú i iné stĺpce prázdnymi, logicky musia byť skryté i tie.
Skryté stĺpce sa stanú neupravovateľnými, lebo nie je možnosť ani vypnúť v nich filter.

Ešte snáď by šlo odkontrolovať, že v ktorom stĺpci bol zapnutý filter na "prázdne" a skryť len ten, v ktorom je aktívny filter. Stĺpce, ktoré sú síce prázdne, ale nie vlastnou vinou , teda tie, v ktorých filter nie je aktívny, by sa pri skrývaní obišli ... Ale v takom prípade by si musel skrývať každý takýto stĺpec jednotlivo, aktivovaním jeho vlastného filtra na "prázdne", čo neviem, či je prínosom... najmä keby tých stĺpcov malo byť viac ...

Takže tu to máš... skrýva sa len ten stĺpec, v ktorom je aktívny výber prázdnych buniek.
Ostatné stĺpce, u ktorých nastane prázdny stav vplyvom filtrácie v inom stĺpci, nebudú dotknuté.

Z dôvodu, že u skrytého stĺpca nie je možné zmeniť filter, aby sa mohol zmeniť výber na neprázdne hodnoty, musí ostať k dispozícii tlačidlo anulácie globálneho filtra, nastoľujúceho východzí stav.
Nie som si istý, či si chcel dosiahnuť toto, lebo predtým si sa vyjadroval tak, že to po úprave NOW na SUBTOTAL pracovalo bez problémov a vtedy sa skrývali všetky prázdne stĺpce...

Tak som si to prečítal celé ešte raz a došlo mi, že ani toto nebude to pravé orechové... Tak ešte bude ďalší pokus...

No ale najhoršie je zasahovanie iného užívateľa, čo je úplne logické. Veď akonáhle manipuluje s filtrom, musí sa zmena filtra (a teda prebehnutie procedúry a teda aj zobrazenie nového stavu) prejaviť u prvého užívateľa. Obávam sa, že na takýto typ použitia Excel pre Teba nebude vhodný.

To isté (preblikávanie, spúšťanie každým úkonom a ovplyvnenie iným užívateľom) sa prejaví úplne totožným spôsobom i u riešenia poskytnutého podporou Microsoftu a riešenie je totožné ako som uviedol pre môj kód, vrátane toho, že problém s viacužívateľským používaním ostane. Uvedený kód totiž pracuje rovnakým princípom, len je krajšie zapísaný.

Nnno, tak myslím, že to mám

Tlačidlo globálnej anulácie filtra som ponechal, myslím, že sa zíde, aj keď už nie je potrebné pre nápravu chyby, lebo k tej by nemalo dochádzať: výber prázdnych stĺpcov už nie je možný.
Spúšťacia bunka G1 (spôsobujúca prepočet hárka) naďalej obsahuje vzorec, len je prekrytá tlačidlom.
Pokiaľ dôjde k rozšíreniu počtu stĺpcov, je potrebné upraviť rozsah v kóde a to nielen v module hárka (kód pridelený udalosti prepočtu hárka) ale aj vo všeobecnom module (kód pridelený tlačidlu). Aj to by šlo zovšeobecniť, ale znamenalo by to ešte potrebu času na testovanie, ktorý momentálne nemám)

Vyskúšaj.