Dynamické oblasti

Poslední vyplněný řádek

Definice

Metoda funguje podobně jako stisknutí CTRL + ↑ v Excelu u daného sloupce. VBA půjde od posledního možného řádku v Excelu až do posledního vyplněného řádku v rámci dat daného sloupce. Následně lze s daty dynamicky pracovat, tedy pokud se makro použije na jiných datech s více nebo méně řádky, vždy bude fungovat pro všechny vyplněná data. Pokud by poslední vyplněný řádek byl na řádku 20, v proměnné LR bude hodnota 20.

Syntaxe

LR = Cells(Rows.Count, 1).End(xlUp).Row

Příkazy

Rows.Count – Vrací celkový počet řádků.

1 – Číslo sloupce, na kterém má být zjištěn poslední vyplněný řádek. Lze zadat číslem nebo i písmenem v uvozovkách, např. „A“.

.End(xlUp) – Posune se nahoru, dokud nenajde první neprázdnou buňku. Pokud by se mělo jít od vrchu do spodu, použila by se možnost xlDown.

.Row – Vrátí číslo posledního vyplněného řádku.

Použití

Chtějme zjistit poslední vyplněný řádek v sloupci „A“. Následně budeme chtít celou oblast nakopírovat do sloupce „B“

LR = Cells(Rows.Count, 1).End(xlUp).Row

Range(„A1:A“ & LR).Copy Destination:=Range(„B1“)

Chtějme projít cyklem všechna vyplněná data v sloupci „B“ a zobrazit je do okna Inmediate pomocí Debug.Print.

LR = Cells(Rows.Count, „B“).End(xlUp).Row

For i = 1 to LR

Debug.Print Cells(i, „B“).Value

Next i

Alternativa hledání posledního vyplněného sloupce

LR = LastRow_0 = ws.Cells.Find(What:=“*“, After:=ws.Range(„A1“), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row

.Find – Hledání v buňkách na listu ws.

What:= „*“ – Co má být nalezeno. Pomocí * bude hledaný jakýkoliv znak v buňkách.

After:=ws.Range(„A1“) – Hledání do určité buňky od konce do buňky A1.

LookAt:=xlPart – Hledání jakékoli části.

LookIn:=xlFormulas – Hledání vzorců v buňkách.

SearchOrder:=xlByRows – Hledání po řádcích.

SearchDirection:=xlPrevious – Hledání zpět, tedy od úplně posledního řádku po první.

MatchCase:=False – Nerozlišování velkých a malých písmen.

.Row – Vrací číslo řádku, kde byla nalezena poslední neprázdná buňka.

LastRow = ws.Range(„MyNameRange“).Row – Zjištění posledního vyplněného řádku pojmenované oblasti.

První vyplněný řádek

Definice

Metoda funguje podobně jako stisknutí CTRL + šipka dolů v Excelu u daného sloupce. VBA půjde od prvního možného řádku v Excelu až do nalezení prvního řádku s daty v rámci daného sloupce. Následně lze s daty dynamicky pracovat, tedy pokud se makro použije na jiných datech, kde data začínají v jiném řádku, makro bude dále fungovat. Pokud by tedy první vyplněný řádek byl na 2. řádku, výstupem bude hodnota 2. Je na výběr, od kterého řádku se má začít hledat první vyplněný řádek.

Syntaxe

FR = Cells(1, „A„).End(xlDown).Row

Příkazy

Chtějme zjistit první i poslední vyplněný řádek v sloupci „A“. Následně budeme chtít celou oblast nakopírovat do sloupce „B“.

FR = Cells(1, 1).End(xlDown).Row

LR = Cells(Rows.Count, 1).End(xlUp).Row

Range(Cells(FR, „A“), Cells(LR, „A“)).Copy Destination:=Range(„B1“)

Chtějme projít cyklem všechna vyplněná data v sloupci „B“ a zobrazit je do okna Inmediate pomocí Debug.Print.

FR = Cells(1, 1).End(xlDown).Row

LR = Cells(Rows.Count, „B“).End(xlUp).Row

For i = FR to LR

Debug.Print Cells(i, „B“).Value

Next i

Poslední vyplněný sloupec

Definice

Metoda funguje podobně jako stisknutí CTRL + šipka doleva v Excelu u daného řádku. VBA půjde od posledního možného sloupce v Excelu až do posledního vyplněného sloupce v rámci dat daného řádku. Následně lze s daty dynamicky pracovat, tedy pokud se makro použije na jiných datech s více nebo méně vyplněnými sloupci, vždy bude fungovat pro všechny vyplněná data. Pokud by poslední vyplněný sloupec byl na sloupci „E“, v proměnné LC bude hodnota 5.

Syntaxe

LC = Cells(1, Columns.Count).End(xlToLeft).Column

Příkazy

1 – Číslo řádku, na kterém má být zjištěn poslední vyplněný sloupec. Pokud máme data hned od prvního řádku, kde je záhlaví, dává největší smysl hledání použít na něm.

Columns.Count – Vrací celkový počet sloupců.

.End(xlToLeft) – Posune se doleva, dokud nenajde první neprázdnou buňku. Pokud by se mělo jít zprava doleva, použila by se možnost xlToRight.

.Column – Vrátí číslo posledního vyplněného sloupce.

Použití

Chtějme zjistit poslední vyplněný sloupec v prvním řádku. Následně budeme chtít celou oblast nakopírovat do druhého řádku

LC = Cells(1, Columns.Count).End(xlToLeft).Column

Range(Cells(1, 1), Cells(1, LC)).Copy Destination:=Range(„A2“)

Chtějme projít cyklem všechna vyplněná data v prvním řádku a zobrazit je do okna Inmediate pomocí Debug.Print.

LC = Cells(1, Columns.Count).End(xlToLeft).Column

For i = 1 to LC

Debug.Print Cells(1, i).Value

Next i

Chtějme zkopírovat dynamicky všechna data od první buňky do posledního vyplněného řádku a sloupce a vložit na další list. Kód bude fungovat i na jiných datech, pokud bude více nebo méně vyplněných sloupců a řádků.

Dim wsSource As Worksheet

Dim wsDestination As Worksheet

Set wsSource = ThisWorkbook.Sheets(1)

Set wsDestination = ThisWorkbook.Sheets(2)

LR = wsSource.Cells(wsSource.Rows.Count, 1).End(xlUp).Row

LC = wsSource.Cells(1, wsSource.Columns.Count).End(xlToLeft).Column

wsSource.Range(wsSource.Cells(1, 1), wsSource.Cells(LR, LC)).Copy

Destination:=wsDestination.Range(„A1“)

Alternativa hledání posledního vyplněného sloupce

LC = ws.Cells.Find(What:=“*“, After:=ws.Range(„A1“), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column

.Find – Hledání v buňkách na listu ws.

What:= „*“ – Co má být nalezeno. Pomocí * bude hledaný jakýkoliv znak v buňkách.

After:=ws.Range(„A1“) – Hledání do určité buňky od konce do buňky A1.

LookAt:=xlPart – Hledání jakékoli části.

LookIn:=xlFormulas – Hledání vzorců v buňkách.

SearchOrder:=xlByColumns – Hledání po sloupcích.

SearchDirection:=xlPrevious – Hledání zpět, tedy od úplně posledního sloupce po první.

MatchCase:=False – Nerozlišování velkých a malých písmen.

.Column – Vrací číslo sloupce, kde byla nalezena poslední neprázdná buňka.

LastColumn = ws.Range(„MyNameRange“).Column – Zjištění posledního vyplněného sloupce pojmenované oblasti.

První vyplněný sloupec

Definice

Metoda funguje podobně jako stisknutí CTRL + šipka doprava v Excelu u daného řádku. VBA půjde od prvního sloupce v Excelu až do prvního vyplněného sloupce v rámci dat daného řádku. Následně lze s daty dynamicky pracovat, tedy pokud se makro použije na jiných datech s více nebo méně vyplněnými sloupci, vždy bude fungovat pro všechny vyplněná data. Pokud by první vyplněný sloupec byl na sloupci „E“, v proměnné FC bude hodnota 5.

Syntaxe

FC = Cells(1, 1).End(xlToRight).Column

Příkazy

1 – Číslo řádku, na kterém má být zjištěn první vyplněný sloupec. Pokud máme data hned od prvního řádku, kde je záhlaví, dává největší smysl hledání použít na něm.

1 – Hledat se má od prvního sloupce.

.End(xlToRight) – Posune se doprava, dokud nenajde první neprázdnou buňku. Pokud by se mělo jít zleva doprava, použila by se možnost xlToLeft.

.Column – Vrátí číslo prvního vyplněného sloupce.

Použití

Chtějme zjistit poslední a první vyplněný sloupec v prvním řádku. Následně budeme chtít celou oblast nakopírovat do druhého řádku

FC = Cells(1, 1).End(xlToRight).Column

LC = Cells(1, Columns.Count).End(xlToLeft).Column

Range(Cells(1, FC), Cells(1, LC)).Copy Destination:=Cells(2, FC)

Chtějme projít cyklem všechna vyplněná data v prvním řádku a zobrazit je do okna Inmediate pomocí Debug.Print.

FC = Cells(1, 1).End(xlToRight).Column

LC = Cells(1, Columns.Count).End(xlToLeft).Column

For i = FC to LC

Debug.Print Cells(1, i).Value

Next i

Chtějme zkopírovat dynamicky všechna data od prvního nalezeného řádku a sloupce v datech do posledního vyplněného řádku a sloupce a vložit na další list. Kód bude fungovat i na jiných datech, pokud bude více nebo méně vyplněných sloupců a řádků.

Dim wsSource As Worksheet

Dim wsDestination As Worksheet

Set wsSource = ThisWorkbook.Sheets(1)

Set wsDestination = ThisWorkbook.Sheets(2)

FC = Cells(1, 1).End(xlToRight).Column

LC = wsSource.Cells(1, wsSource.Columns.Count).End(xlToLeft).Column

FR = Cells(1, FC).End(xlDown).Row

LR = wsSource.Cells(wsSource.Rows.Count, FC).End(xlUp).Row

wsSource.Range(wsSource.Cells(FR, FC), wsSource.Cells(LR, LC)).Copy

Destination:=wsDestination.Range(„A1“)

Dynamické označení dat

Mějme zjištěné poslední vyplněné řádky a sloupce:

LR = wsSource.Cells(wsSource.Rows.Count, 1).End(xlUp).Row

LC = wsSource.Cells(1, wsSource.Columns.Count).End(xlToLeft).Column

Range(Cells(1, 1), Cells(LR, LC)).Select – Dynamické označení všech dat.

Range(Cells(1, 1), Cells(LR, 1)).Select – Dynamické označení prvního sloupce.

Cells(LR + 1, 1).Value = „Nová hodnota“ – Přidání nových dat do prvního volného řádku prvního sloupce.

CurrentRegion

Definice

Funguje stejně jako Ctrl + A, když je kurzor uvnitř oblasti dat, označí všechna data (bez prázdných řádků nebo sloupců).

Praktické vysvětlení

Možnost je vhodné použít v případě, kdy je nutné označit celou tabulku bez ohledu na její velikost, tedy počet vyplněných řádků a sloupců.

Pokud jsou data v nějaké oblasti do 100 řádků a řádek 20 by byl celý prázdný, na tomto řádku se přeruší vývěr a nebudou tedy označena všechna data pod tím, ačkoliv by byla vyplněna, jelikož je zde již celý prázdný řádek. Stejně tak i u sloupců.

Použití

Označení všech vyplněných dat v oblasti:

Set oblast = Range(„A1“).CurrentRegion

oblast.Select

Získání počtu řádků v dané oblasti:

radky = Range(„A1“).CurrentRegion.Rows.Count

sloupce = Range(„A1“).CurrentRegion.Columns.Count

UsedRange

Definice

Označí celou oblast, která byla někdy použita (i smazané buňky mohou zůstat „označené“).

Praktické vysvětlení

Možnost se používá v případě, kdy je nutné pracovat se všemi vyplněnými buňkami v listu

Pozor na smazané buňky, které stále mohou být v UsedRange.

Použití

Označení vyplněné oblasti na aktivním listu:

Set oblast = ActiveSheet.UsedRange

oblast.Select