Telefonszámunk: 1-472-0679

Kijelölések

Tartománynevek elrejtése – VBA

A professzionális Excel felhasználók gyakran dolgoznak névtartományokkal. Ez ugyebár azt jelenti, hogy egy tartományt elnevezünk, és a képletekben a tartomány címe helyett a nevét használjuk. A tartományok elnevezését a szerkesztőléc bal oldalán lévő Név mezőben vagy a Képletek szalagon található Névkezelőben lehet megadni. A Dinamikus névtartományokról olvashatsz […]

Tovább...

Az első kitöltött cella megkeresése makróval

Előfordulhat, hogy egy makró projekt kapcsán szembejön az a probléma, hogy nem tudjuk előre azt, hogy hol is kezdődik a feldolgozandó tartomány, vagy az éppen mindig változó. Emiatt jól jöhet ez az egy soros snippet a probléma megoldására.

[vb]

Range("A1").Select

Cells.Find(What:="*").Activate

[/vb]

Az első sor az aktuális munkalap első celláját aktiválja, hogy onnan kezdődjön a keresés. A második pedig megkeresi az első kitöltött cellát, amiben bármilyen érték lehet, szám, vagy szöveg, akár képlet.

A fenti kód csak akkor működik, ha az aktuális munkalap nem teljesen üres. Ellenkező esetben hibát fog jelezni.

Ezért csak akkor használjuk így, ha biztosan tudjuk, hogy van kitöltött cella a vizsgált munkalapon.

Munkalap ürességének ellenőrzése

Készítünk egy függvényt, ami paraméterként a vizsgálandó munkalapot kapja meg, vissza pedig egy logikai értéket ad.

Ha egy munkalap üres, akkor a használt tartomány címe az ‘A1’ lesz. És ha az üres, akkor a munkalapot üresnek tekinthetjük.

[vb]

Function is_empty_sheet(sname As Worksheet) As Boolean

is_empty_sheet = sname.UsedRange.Address = "$A$1" And IsEmpty(sname.Range("A1"))

End Function

[/vb]

A javított kód

[vb]
Function is_empty_sheet(sname As Worksheet) As Boolean

is_empty_sheet = sname.UsedRange.Address = "$A$1" And IsEmpty(sname.Range("A1"))

End Function

Sub find_first_filled()

If Not is_empty_sheet(ActiveSheet) Then

Range("A1").Select
Cells.Find(What:="*").Activate
End If

End Sub

[/vb]

Így az első kitöltött cella keresése csak akkor fut le, ha az aktuális munkalap nem üres.

A munkalap nem csak cellákat, hanem egyéb elemet is tartalmazhat, pl.: Grafikonok, rajz elemek, képek, stb..

Ezeket ez a kód nem vizsgálja

 

Mindez működés közben

  • Hol az első kitöltött cella?

Tovább...

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

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.

[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

Tovább...

Üres stringet tartalmazó cellák kijelölése vagy törlése

Ha egy tartományban képlettel számolunk, akkor nem tudunk visszaadni üres cellát. Leggyakrabban nullát, vagy üres stringet adunk vissza, amit az Excel másképp kezel bizonyos esetekben, mint az üres cellát.

A példában, ha az A1-ben nulla van, akkor üres stringet, különben 1-et adunk vissza:

=IF(A1=0,"",1)

Tegyük fel, hogy ez a képlet sok soron keresztül megy, és a végén grafikont kell belőle rajzolni. A problémát az okozza, hoyg az Excel grafikon motorja csak a valóban üres cellákat tekinti annak, és azokat nem rajzolja ki  a diagramra, de az üres stringet nullának tekinti, és kirajzolja.

Egyik megoldás az lehet, hogy a módosítjuk a képletet, és az üres string helyett hibát adunk, amit szintén figyelmen kívül hagy a grafikon.

=IF(A1=0,NA(),1)

Az NA() függvény a #N/A eredményt adja. Magyar megfelelője a HIÁNYZIK() függvény.

Másik lehetőség, hogy kijelöljük az oszlopot, sé egy kis makróval töröljük az összes üres stringet tartalmazó cella tartalmát.

Üres stringeket tartalmazó cellák kiürítése VBA kóddal

[vb]

‘A kijeloles cellái közül törli a tartalmat, ha üres stringet talál benne
Sub delete_empty_strings()
Dim u As Range
Dim cur As Range

For Each cur In Selection.Cells
If cur.Value = "" Then cur.ClearContents
Next

End Sub
[/vb]

Tovább...

Nem összefüggő tartományok kijelölése VBA-ban

Ha szükség lenne nem összefüggő tartományok kijelölésére, akkor ahhoz a union() metódust tudjuk használni. Általános szintaktikája:

dim eredmeny as range

set eredmeny = UNION(range 1, range 2, …range n)

eredmeny.select

Az egyes tartományokat külön-külön is megadhatjuk az egyesítéshez, de ha ezt előre nem tudjuk, akkor menet közben is felépíthetjük a dolgot úgy, hogy mindig az előző union-hoz adjuk hozzá az aktuális tartományt.

Második és negyedik oszlop kijelölése az aktív tartományban

[vb]

Sub union_1()
Dim oszlop1 As Range
Dim oszlop2 As Range

Set oszlop1 = Range(ActiveCell.CurrentRegion.Cells(2), _
ActiveCell.CurrentRegion.Cells(2).End(xlDown))

Set oszlop2 = Range(ActiveCell.CurrentRegion.Cells(4), _
ActiveCell.CurrentRegion.Cells(4).End(xlDown))

union(oszlop1, oszlop2).Select
End Sub

[/vb]

Az eredmény

Az aktív cellától kezdve minden második sor kijelölése a végéig

[vb]

Sub union_2()
Dim akt_sor As Range
Dim osszes_sor As Range
Dim uo As Integer
Dim us As Long
Dim vege As Boolean

‘Sor vége, azaz utolsó oszlop
uo = ActiveCell.CurrentRegion.SpecialCells(xlCellTypeLastCell).Column
‘Utolsó sor
us = ActiveCell.CurrentRegion.SpecialCells(xlCellTypeLastCell).Row

‘Első Aktualis sor kijelolése
Set akt_sor = Range(ActiveCell, Cells(ActiveCell.Row, uo))

‘Kezdetben az összes kijelölés egyenlő az első aktuális sorral
Set osszes_sor = akt_sor

vege = False
‘Ciklus, amíg a tartomány végéig nem érünk
While vege <> True

‘Két sorral lejjebb lépünk, és kijelöljük a következő sort
ActiveCell.Offset(2, 0).Select

Set akt_sor = Range(ActiveCell, Cells(ActiveCell.Row, uo))

‘Ha még nem értünk a végére
If ActiveCell.Row <= us Then
‘Összeadjuk az eddigi kijelöléseket és az aktuális sort
Set osszes_sor = union(osszes_sor, akt_sor)
Else
vege = True
End If
Wend

osszes_sor.Select

End Sub

[/vb]

Eredmény

Továbbiak

Az így kijelölt tartományokkal SELECTION néven dolgozhatunk tovább, megformázhatjuk, képlettel tölthetjük fel, törölhetjük, stb…

Tovább...