Práce s buňkami a rozsahy v Excelu VBA (Select, Copy, Move, Edit)

při práci s Excelem je většina času strávena v oblasti pracovního listu – zabývající se buňkami a rozsahy.

a pokud chcete automatizovat práci v Excelu pomocí VBA, musíte vědět, jak pracovat s buňkami a rozsahy pomocí VBA.

existuje mnoho různých věcí, které můžete dělat s rozsahy ve VBA (například vybrat, kopírovat, přesouvat, upravovat atd.).

abych pokryl toto téma, rozbiju tento tutoriál do sekcí a ukážu vám, jak pracovat s buňkami a rozsahy v Excelu VBA pomocí příkladů.,

začněme.

Pokud máte zájem o učení VBA snadný způsob, podívejte se na můj online Excel VBA školení.

výběr buňky / rozsahu v Excelu pomocí VBA

pro práci s buňkami a rozsahy v Excelu pomocí VBA, nemusíte jej vybrat.

ve většině případů je lepší vybrat buňky nebo rozsahy (jak uvidíme).,

navzdory tomu je důležité projít touto sekcí a pochopit, jak to funguje. To bude rozhodující ve vašem učení VBA a v tomto tutoriálu bude použito mnoho konceptů, na které se zde vztahuje.

takže začněme velmi jednoduchým příkladem.

Výběr jedné Buňky Pomocí VBA

chcete-Li vybrat jednu buňku v aktivním listu (řekněme A1), pak můžete použít níže kód:

Sub SelectCell()Range("A1").SelectEnd Sub

výše uvedený kód je povinné ‚Sub“ a „End Sub‘ část, a řádek kódu, který vybere buňky A1.,

Range(„A1“) říká VBA adresu buňky, na kterou chceme odkazovat.

Select je metoda objektu rozsahu a vybere buňky / rozsah zadaný v objektu rozsahu. Odkazy na buňky musí být uzavřeny ve dvojitých uvozovkách.

tento kód by vykazoval chybu v případě, že list grafu je aktivní list. List grafu obsahuje grafy a není široce používán. Vzhledem k tomu, že v něm nejsou buňky/rozsahy, výše uvedený kód jej nemůže vybrat a nakonec se zobrazí chyba.

Všimněte si, že protože chcete vybrat buňku v aktivním listu, stačí zadat adresu buňky.,

ale pokud chcete vybrat buňku v jiném listu (řekněme Sheet2), musíte nejprve aktivovat Sheet2 a poté v ní vybrat buňku.

Sub SelectCell()Worksheets("Sheet2").ActivateRange("A1").SelectEnd Sub

podobně můžete také aktivovat sešit, poté v něm aktivovat konkrétní pracovní list a poté vybrat buňku.

Sub SelectCell()Workbooks("Book2.xlsx").Worksheets("Sheet2").ActivateRange("A1").SelectEnd Sub

Všimněte si, že když odkazujete na sešity, musíte použít celé jméno spolu s příponou souboru (.xlsx ve výše uvedeném kódu). V případě, že sešit nebyl nikdy uložen, nemusíte používat příponu souboru.,

nyní tyto příklady nejsou příliš užitečné, ale později v tomto tutoriálu uvidíte, jak můžeme použít stejné koncepty pro kopírování a vkládání buněk v Excelu (pomocí VBA).

stejně jako vybereme buňku, můžeme také vybrat rozsah.

v případě rozsahu by to mohl být rozsah pevných velikostí nebo rozsah proměnných velikostí.

v pevném rozsahu velikostí byste věděli, jak velký je rozsah, a můžete použít přesnou velikost kódu VBA. Ale s rozsahem s proměnnou velikostí nemáte tušení, jak velký je rozsah, a musíte použít trochu magie VBA.

podívejme se, jak to udělat.,

výběr rozsahu velikosti Fix

zde je kód, který vybere rozsah A1: D20.

Sub SelectRange()Range("A1:D20").SelectEnd Sub

Další způsob, jak to udělat, je pomocí níže uvedeného kódu:

Sub SelectRange()Range("A1", "D20").SelectEnd Sub

výše uvedený kód má vlevo nahoře adresa buňky (A1) a pravé dolní buňky adresou (D20) a vybere celou řadu. Tato technika se stává užitečnou, když pracujete s variabilně velkými rozsahy (jak uvidíme, kdy je koncová vlastnost pokryta později v tomto tutoriálu).,

Pokud chcete, aby se výběr uskutečnil v jiném sešitu nebo jiném listu, musíte VBA sdělit přesné názvy těchto objektů.

například níže uvedený kód vybere rozsah A1:D20 v listu Sheet2 v sešitu Book2.

Sub SelectRange()Workbooks("Book2.xlsx").Worksheets("Sheet1").ActivateRange("A1:D20").SelectEnd Sub

nyní, co když nevíte, kolik řádků je. Co když chcete vybrat všechny buňky, které mají hodnotu v něm.

v těchto případech musíte použít metody uvedené v další části(při výběru rozsahu variabilně velké).,

výběr variabilně velkého rozsahu

existují různé způsoby, jak můžete vybrat řadu buněk. Zvolená metoda bude záviset na tom, jak jsou data strukturována.

v této části se budu zabývat některými užitečnými technikami, které jsou opravdu užitečné při práci s rozsahy ve VBA.

Vyberte Pomocí CurrentRange Majetku

V případech, kdy nevíte, kolik řádků/sloupců dat, můžete použít CurrentRange vlastnost objektu Range.

vlastnost CurrentRange pokrývá všechny sousedící naplněné buňky v datovém rozsahu.,

níže je kód, který vybere aktuální oblast, která drží buňku A1.

Sub SelectCurrentRegion()Range("A1").CurrentRegion.SelectEnd Sub

výše uvedená metoda je dobrá, pokud máte všechna data jako tabulku bez prázdných řádků/sloupců.

ale v případě, že máte v datech prázdné řádky/sloupce, nevybere ty po prázdných řádcích/sloupcích. Na obrázku níže, CurrentRegion kód vybere data do řádku 10 jako řádek 11 je prázdný.

v takových případech můžete použít vlastnost UsedRange objektu listu.,

zvolte Usedrange vlastnost

UsedRange umožňuje odkazovat na všechny buňky, které byly změněny.

takže níže uvedený kód vybere všechny použité buňky v aktivním listu.

Sub SelectUsedRegion()ActiveSheet.UsedRange.SelectEnd Sub

Všimněte si, že v případě, že máte dalekou buňku, která byla použita, bude to považováno výše uvedeným kódem a všechny buňky, dokud nebude vybrána použitá buňka.

vyberte pomocí koncové vlastnosti

nyní je tato část opravdu užitečná.

koncová vlastnost umožňuje vybrat poslední vyplněnou buňku., To vám umožní napodobit účinek ovládání kláves se šipkou dolů/nahoru nebo ovládat pravé/levé klávesy.

zkusme to pochopit pomocí příkladu.

Předpokládejme, že máte soubor údajů, jak je uvedeno níže, a vy chcete rychle vybrat poslední vyplněné buňky ve sloupci A.

problém je, že údaje mohou změnit, a nevíte, kolik buněk jsou vyplněny. Pokud to musíte provést pomocí klávesnice, můžete vybrat buňku A1 a poté použít klávesu CTRL + šipka dolů a ve sloupci vybere poslední vyplněnou buňku.

nyní se podívejme, jak to provést pomocí VBA., Tato technika přijde vhod, když chcete rychle přejít na poslední vyplněné buňky v různě velké sloupce

Sub GoToLastFilledCell()Range("A1").End(xlDown).SelectEnd Sub

výše uvedený kód by skok na poslední vyplněné buňky ve sloupci A.

Podobně můžete použít do Konce(xlToRight) pro skok na poslední vyplněné buňky v řádku.

Sub GoToLastFilledCell()Range("A1").End(xlToRight).SelectEnd Sub

nyní, co když chcete vybrat celý sloupec namísto skákání do poslední naplněné buňky.,

můžete to udělat pomocí kódu níže:

Sub SelectFilledCells()Range("A1", Range("A1").End(xlDown)).SelectEnd Sub

Ve výše uvedeném kódu, jsme využili první a poslední odkaz na buňku, které musíme zvolit. Bez ohledu na to, kolik vyplněných buněk je, výše uvedený kód vybere vše.

Zapamatujte si výše uvedený příklad, kde jsme vybrali rozsah A1: D20 pomocí následujícího řádku kódu:

rozsah(„A1″,“D20“)

zde A1 byla levá horní buňka a d20 byla pravá dolní buňka v rozsahu. Stejnou logiku můžeme použít při výběru variabilně velkých rozsahů., Ale protože neznáme přesnou adresu pravé dolní buňky, použili jsme koncovou vlastnost, abychom ji získali.

v rozsahu („A1“, rozsah („A1“).Konec (xlDown)), „A1“ označuje první buňku a rozsah („A1“).Konec (xlDown) označuje poslední buňku. Protože jsme poskytli oba odkazy, metoda Select vybere všechny buňky mezi těmito dvěma odkazy.

podobně můžete také vybrat celou datovou sadu, která má více řádků a sloupců.

níže uvedený kód vybere všechny vyplněné řádky / sloupce začínající od buňky A1.,

Sub SelectFilledCells()Range("A1", Range("A1").End(xlDown).End(xlToRight)).SelectEnd Sub

ve výše uvedeném kódu jsme použili Range(„A1“).Konec (xlDown).Konec (xlToRight) získat odkaz na pravé dolní naplněné buňky datové sady.

Rozdíl mezi Použitím CurrentRegion a End

Pokud vás zajímá, proč používat Konci vlastnost vyberte plný rozsah, když máme vlastnost CurrentRegion, dovolte mi, abych vám říct rozdíl.

s koncovou vlastností můžete zadat počáteční buňku., Například pokud máte data v A1: D20, ale první řádek jsou záhlaví, můžete pomocí vlastnosti End vybrat data bez záhlaví (pomocí níže uvedeného kódu).

Sub SelectFilledCells()Range("A2", Range("A2").End(xlDown).End(xlToRight)).SelectEnd Sub

ale CurrentRegion automaticky vybere celý datový soubor, včetně záhlaví.

zatím jsme v tomto tutoriálu viděli, jak odkazovat na řadu buněk různými způsoby.

nyní se podívejme na některé způsoby, jak můžeme tyto techniky skutečně použít k dokončení nějaké práce.,

kopírovat buňky / rozsahy pomocí VBA

Jak jsem zmínil na začátku tohoto tutoriálu, výběr buňky není nutné provádět akce na něm. V této části uvidíte, jak kopírovat buňky a rozsahy, aniž byste je vybrali.

začněme jednoduchým příkladem.

kopírování jedné buňky

Pokud chcete zkopírovat buňku A1 a vložit ji do buňky D1, níže uvedený kód by to udělal.

Sub CopyCell()Range("A1").Copy Range("D1")End Sub

Všimněte si, že metoda kopírování objektu range kopie buňky (stejně jako Ctrl +C) a vloží je do zadaného cíle.,

ve výše uvedeném příkladu kódu je cíl zadán ve stejném řádku, kde používáte metodu kopírování. Pokud chcete, aby váš kód ještě více čitelný, můžete použít níže kód:

Sub CopyCell()Range("A1").Copy Destination:=Range("D1")End Sub

výše uvedené kódy budou zkopírujte a vložte hodnotu, stejně jako formátování/vzorce.

jak jste si možná již všimli, výše uvedený kód zkopíruje buňku bez jejího výběru. Bez ohledu na to, kde jste na listu, kód zkopíruje buňku A1 a vloží ji na D1.

také si všimněte, že výše uvedený kód přepíše jakýkoli existující kód v buňce D2., Pokud chcete, aby vám aplikace Excel dala vědět, zda v buňce D1 již něco existuje, aniž byste jej přepsali, můžete použít níže uvedený kód.

Kopírování Opravit Velké Rozpětí

Pokud chcete kopírovat A1:D20 v J1:M20, můžete použít níže kód:

Sub CopyRange()Range("A1:D20").Copy Range("J1")End Sub

V cílové buňce, stačí zadat adresu levé horní buňky. Kód by automaticky zkopíroval přesný kopírovaný rozsah do cíle.

stejný konstrukt můžete použít ke kopírování dat z jednoho listu do druhého.

níže uvedený kód zkopíruje A1: D20 z aktivního listu na List2.,

Sub CopyRange()Range("A1:D20").Copy Worksheets("Sheet2").Range("A1")End Sub

výše uvedené zkopíruje data z aktivního listu. Před spuštěním kódu se tedy ujistěte, že list, který má data, je aktivní list. Chcete-li být v bezpečí, můžete také zadat název listu při kopírování dat.

Sub CopyRange()Worksheets("Sheet1").Range("A1:D20").Copy Worksheets("Sheet2").Range("A1")End Sub

dobrá věc, o výše uvedeném kódu je, že bez ohledu na to, který list je aktivní, to bude vždy kopírovat data z List1 a vložte jej v listu s názvem List2.

pojmenovaný rozsah můžete také zkopírovat pomocí jeho názvu namísto odkazu.,

Pokud máte například pojmenovaný rozsah s názvem „SalesData“, můžete pomocí níže uvedeného kódu zkopírovat tato data do Sheet2.

Sub CopyRange()Range("SalesData").Copy Worksheets("Sheet2").Range("A1")End Sub

Pokud rozsah platnosti pojmenované oblasti je celý sešit, nemusíte být na listu, který má pojmenovanou oblast spuštění tohoto kódu. Od pojmenovaný rozsah je rozsahem pro sešit, můžete přistupovat z libovolného listu pomocí tohoto kódu.

Pokud máte tabulku s názvem Table1, můžete ji zkopírovat do Sheet2 pomocí níže uvedeného kódu.

Sub CopyTable()Range("Table1").Copy Worksheets("Sheet2").Range("A1")End Sub

můžete také zkopírovat rozsah do jiného sešitu.,

v následujícím příkladu zkopíruji tabulku Excel (Table1) do sešitu Book2.

Sub CopyCurrentRegion()Range("Table1").Copy Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1")End Sub

tento kód by fungoval pouze v případě, že je sešit již otevřený.

Kopírování Proměnné Velikosti Rozpětí

Jeden způsob, jak kopírovat proměnné velikosti se pohybuje, je převést tyto do pojmenované oblasti nebo Tabulce Excel a používat kódy jak je uvedeno v předchozí části.

ale pokud to nemůžete udělat,můžete použít CurrentRegion nebo koncovou vlastnost objektu range.

níže uvedený kód zkopíruje aktuální oblast do aktivního listu a vloží ji do listu 2.,

Sub CopyCurrentRegion()Range("A1").CurrentRegion.Copy Worksheets("Sheet2").Range("A1")End Sub

Pokud chcete kopírovat první sloupec ze souboru dat do poslední vyplněné buňky a vložit jej do List2, můžete použít níže kód:

Sub CopyCurrentRegion()Range("A1", Range("A1").End(xlDown)).Copy Worksheets("Sheet2").Range("A1")End Sub

Pokud chcete kopírovat řádky i sloupce, můžete použít níže uvedený kód:

Sub CopyCurrentRegion()Range("A1", Range("A1").End(xlDown).End(xlToRight)).Copy Worksheets("Sheet2").Range("A1")End Sub

Všimněte si, že všechny tyto kódy nemyslím, vyberte buňky, zatímco dostat popraven. Obecně najdete jen několik případů, kdy musíte před zahájením práce vybrat buňku/rozsah.,

přiřazování rozsahů objektovým proměnným

zatím používáme úplnou adresu buněk (např. sešity („Book2.xlsx“).Pracovní Listy („Sheet1“).Rozsah(„A1“)).

Chcete-li, aby byl váš kód lépe zvládnutelný, můžete tyto rozsahy přiřadit objektovým proměnným a poté použít tyto proměnné.

například, v níže kód, mám přiřazené zdroje a určení rozsahu objektu proměnné a pak použít tyto proměnné, aby kopírování dat z jednoho rozsahu na druhý.

začneme deklarováním proměnných jako objektů rozsahu., Poté přiřazíme rozsah těmto proměnným pomocí příkazu Set. Jakmile je rozsah přiřazen proměnné, můžete proměnnou jednoduše použít.

zadejte Data do další prázdné buňky (pomocí vstupního pole)

pomocí vstupních polí můžete uživateli povolit zadávání dat.

například předpokládejme, že máte níže uvedená data a chcete zadat prodejní záznam, můžete použít vstupní pole ve VBA. Pomocí kódu se můžeme ujistit, že vyplní data v dalším prázdném řádku.,

výše uvedený kód používá vstupní pole VBA k získání vstupů od uživatele a poté zadá vstupy do zadaných buněk.

Všimněte si, že jsme nepoužívali přesné odkazy na buňky. Místo toho jsme použili vlastnost End a Offset k nalezení poslední prázdné buňky a vyplnění dat v ní.

tento kód není zdaleka použitelný. Pokud například zadáte textový řetězec, když vstupní pole požádá o množství nebo částku, všimnete si, že to aplikace Excel umožňuje. Můžete použít podmínku If ke kontrole, zda je hodnota číselná nebo ne, a poté ji podle toho povolit.,

smyčkování buněk / rozsahů

zatím jsme viděli, jak vybrat, kopírovat a zadávat data do buněk a rozsahů.

v této části uvidíme, jak procházet sadou buněk/řádků/sloupců v rozsahu. To by mohlo být užitečné, pokud chcete analyzovat každou buňku a provést na ní nějakou akci.

například, pokud chcete zvýraznit každý třetí řádek ve výběru, musíte projít a zkontrolovat číslo řádku., Podobně, pokud chcete zvýraznit všechny negativní buňky změnou barvy písma na červenou, musíte projít a analyzovat hodnotu každé buňky.

zde je kód, který bude procházet řádky ve vybraných buňkách a zvýrazňovat alternativní řádky.

Sub HighlightAlternateRows()Dim Myrange As RangeDim Myrow As RangeSet Myrange = SelectionFor Each Myrow In Myrange.RowsIf Myrow.Row Mod 2 = 0 ThenMyrow.Interior.Color = vbCyanEnd IfNext MyrowEnd Sub

výše uvedený kód používá funkci MOD ke kontrole čísla řádku ve výběru. Pokud je číslo řádku rovnoměrné, zvýrazní se azurovou barvou.

zde je další příklad, kdy kód prochází každou buňkou a zvýrazňuje buňky, které v ní mají zápornou hodnotu.,

Sub HighlightAlternateRows()Dim Myrange As RangeDim Mycell As RangeSet Myrange = SelectionFor Each Mycell In MyrangeIf Mycell < 0 ThenMycell.Interior.Color = vbRedEnd IfNext MycellEnd Sub

Všimněte si, že můžete udělat totéž pomocí Podmíněného Formátování (které je dynamické a lepší způsob, jak to udělat). Tento příklad slouží pouze k tomu, aby vám ukázal, jak looping funguje s buňkami a rozsahy ve VBA.

kam umístit kód VBA

zajímá Vás, kde kód VBA jde do sešitu aplikace Excel?

Excel má backend VBA nazvaný editor VBA. Kód musíte zkopírovat a vložit do okna kódu modulu editoru VB.

zde jsou kroky k tomu:

1. přejděte na kartu Vývojář.,
2. klikněte na možnost Visual Basic. Tím se otevře editor VB v backendu.
3. v podokně Project Explorer v editoru VB klepněte pravým tlačítkem myši na libovolný objekt sešitu, do kterého chcete vložit kód. Pokud Průzkumníka projektů nevidíte, přejděte na kartu Zobrazit a klikněte na Průzkumník projektů.
4. přejděte na Vložit a klikněte na modul. Tím se vloží objekt modulu pro sešit.
5. zkopírujte a vložte kód do okna modulu.,

Může se Vám Líbit Následující aplikace Excel Cvičení:

• Práce s Listy pomocí VBA.
• práce s sešity pomocí VBA.
• vytváření uživatelsky definovaných funkcí v Excelu.
• pro další smyčku v Excelu VBA-průvodce pro začátečníky s příklady.
• Jak používat funkci Excel VBA InStr (s praktickými příklady).
• Excel VBA Msgbox.
• jak zaznamenat makro v aplikaci Excel.
• jak spustit makro v aplikaci Excel.
• jak vytvořit doplněk v aplikaci Excel.,
• Excel osobní makro sešit / Uložit & použijte makra ve všech sešitech.
• Excel VBA Events-snadný (a kompletní) průvodce.
• Excel VBA zpracování chyb.
• jak třídit Data v Excelu pomocí VBA (průvodce krok za krokem).
• 24 užitečné příklady Maker aplikace Excel pro začátečníky VBA (připravené k použití).

---