Gyakori feladat, hogy egy szűrt táblázatban az egyik oszlopban az adatokat teljesen vagy részben másikra szeretnénk cserélni. Ezt kézi szerkesztéssel nagyon egyszerű megtenni, de ha a feladatot makró kódon belül szeretnénk megoldani (mert pl. egy nagyobb feladat része), akkor már nem annyira egyértelmű a helyzet.
Volt már egy korábbi anyag arról, hogy ha szűrt tartományba szeretnénk a vágólapról beilleszteni adatokat, de most nem erről van szó, hanem egyszerű felülírásról.
Kézi módszer
Ez nagyon egyszerű, hiszen már jópár verzió óta az Excel kiválóan kezeli ezt a problémát.
- Leszűrjük a táblát
- A módosítandó oszlopban az első elemet átírjuk
- Lehúzzuk az aljáig
Ha megszüntetjük a szűrést, akkor látni fogjuk, hogy csak az előzőleg szűrt adatok módosultak, a közbensők, amik a szűréskor rejtve voltak, nem változtak.
Makró – VBA módszer
Ha az előző műveletsort rögzítjük makróval, akkor a kódból nagyon gyorsan kiderül, hogy általánosan nem használható, tehát változó adattartalom esetén, ahol a szűrés más-más eredményt adna. Emiatt ezt a problémát kicsit komolyabban kell leprogramozni.
Elméleti háttér
Ha egy oszlop szűrt adatokat mutat, és abban módosítani szeretnénk, akkor előbb ki kell jelölni CSAK a látható cellákat.
- Kijelöljük a teljes, szűrt táblázatot (Ctl-A)
- Speciális kijelölés -> Csak a látható cellák
Így viszont az oszlopban lévő adatok szétválnak egymástól, és ha újra kinyitjuk, azaz megszüntetjük a szűrést, akkor több, nem összefüggő tartományt fogunk látni.
Tehát a makróval a kategória oszlopban lévő, látható tartományokat kell módosítani. Példánkban a „Beverages” szót, másik kategória névvel.
A Selection objektum
Ha több, nem összefüggő tartományt jelölünk ki, vagy hozunk létre a fenti módszerrel, attól az még a SELECTION objektummal lefedhető, és egy lépésben átírható a benne lévő összes cella értéke.
A Fejléc problémája
Mivel előre nem tudjuk, hogy milyen sorok fognak beleesni a szűrésbe, legegyszerűbb, ha a fejlécet is belevesszük a kijelölésbe. Viszont így annak az értéke is felülíródik. Ezt úgy oldjuk meg, hogy a felülírás előtt elmentjük a fejléc eredeti értékét, majd a végén visszaírjuk.
A VBA kód
A példában egy általánosabb megoldást adok, ahol a subrutin argumentumként kapja meg azt, hogy melyik oszlopban kell dolgoznia, és mire kell cserélni a szűrt elemeket.
[vb]
Sub Replace_filtered_data(header_cell As Range, new_value As Variant)
Dim h As Variant
‘A fejléc tartalmának megjegyzése
h = header_cell.Value
‘A módosítandó oszlop kijelölése fejléccel együtt
Range(header_cell, header_cell.End(xlDown)).Select
‘Látható cellák kijelölése
Selection.SpecialCells(xlCellTypeVisible).Select
‘A létrejött, nem összefüggő tartomány feltöltése az új adattal
Selection.Value = new_value
‘A fejléc visszaírása
header_cell.Value = h
End Sub
Sub proba()
Call Replace_filtered_data(ActiveCell, Range("csere"))
End Sub
[/vb]
Letölthető gyakorló munkafüzet: szurt_tartomany_modositasa
Az egészet működés közben, magyarázattal együtt láthatod a videóban
Szűrt adatok módosítása makróval
