Telefonszámunk: 1-472-0679

Adatok módosítása szűrt tartományban – makró -VBA

2013-09-29 - horvimi - Megtekintések száma: 2,240 - Kategória: Kijelölések, Macro-VBA, Tartományok kezelése
Hivatkozott Excel verzio: Excel 2007 Excel 2010 Excel 2013

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.

szurt_tartomany

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.

  1. Leszűrjük a táblát
  2. A módosítandó oszlopban az első elemet átírjuk
  3. 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.

  1. Kijelöljük a teljes, szűrt táblázatot (Ctl-A)
  2. Speciális kijelölés -> Csak a látható cellák

Látható cellák kijelölése

szurt_tablazat

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

Szurt-adatok-kijeloles-nem-osszefuggo

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.

A futás előtt feltételezi, hogy már a szűrés megtörtént, és az aktív cella a módosítandó oszlop fejlécén áll.

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

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

Vélemény, hozzászólás?