3 nejlepší způsoby, jak najít poslední neprázdný řádek a sloupec pomocí VBA

Hledání naposledy použitých řádků a sloupců je jedním ze základních a důležitých úkolů jakékoli automatizace v Excelu pomocí VBA. Pro automatické sestavování listů, sešitů a aranžování dat musíte najít limit dat na listech.

Tento článek vysvětlí všechny metody hledání posledního řádku a sloupce v Excelu nejjednoduššími způsoby.
1. Najděte poslední neprázdný řádek ve sloupci pomocí Range.End
Nejprve se podívejme na kód. Vysvětlím to dopisem.

Sub getLastUsedRow () Dim last_row As Integer last_row = Cells (Rows.Count, 1) .End (xlUp) .Row ‘This line gets the last row Debug.Print last_row End Sub

Výše uvedený díl vyhledá poslední řádek ve sloupci 1.

Jak to funguje?
Je to jako jít na poslední řádek v listu a poté stisknout klávesovou zkratku CTRL+UP.
Buňky (řádky. Počet, 1): Tato část vybere buňku ve sloupci A. Řádky. Počet dává 1048576, což je obvykle poslední řádek v listu aplikace Excel.

Buňky (1048576, 1)

.Konec (xlUp): End je metoda třídy rozsahu, která se používá k navigaci v listech na konce. xlUp je proměnná, která udává směr. Tento příkaz společně vybere poslední řádek s daty.

Buňky (počet řádků, 1). Konec (xlUp)

.Řádek : řádek vrátí číslo řádku vybrané buňky. Proto dostaneme číslo řádku poslední buňky s daty ve sloupci A. v našem příkladu je 8.

Podívejte se, jak snadné je najít poslední řádky s daty. Tato metoda vybere poslední řádek v datech bez ohledu na prázdné buňky před ním. Na obrázku vidíte, že data má pouze buňka A8. Všechny předchozí buňky jsou prázdné kromě formátu A4.

Vyberte poslední buňku s daty ve sloupci
Pokud chcete vybrat poslední buňku ve sloupci A, pak odeberte „.row“ z konce a napište .select.

Sub buňky getLastUsedRow () (řádky. Počet, 1). Konec (xlUp). Vyberte „Tento řádek vybere poslední buňku ve sloupci Konec Sub

Příkaz „.Vybrat“ vybere aktivní buňku.
Získat sloupec adresy poslední buňky
Pokud chcete získat adresu poslední buňky ve sloupci A, stačí odstranit „.row“ z konce a napsat .address.

Sub getLastUsedRow () add = Cells (Rows.Count, 1) .End (xlUp) .address ‘Tento řádek vybere poslední buňku ve sloupci Debug.print add End Sub

Rozsah.Adresa funkce vrací adresu aktivní buňky.
Najděte poslední neprázdný sloupec v řadě
Je to téměř stejné jako nalezení poslední neprázdné buňky ve sloupci. Zde získáváme číslo sloupce poslední buňky s údaji v řádku 4.

Sub getLastUsedCol () Dim last_col As Integer last_col = Cells (4, Columns.Count) .End (xlToLeft) .Column ‘This line gets the last column Debug.Print last_col End Sub

Na obrázku vidíte, že vrací číslo posledního neprázdného sloupce buňky v řádku 4. Což je 4.
Jak to funguje?

Mechanika je stejná jako hledání poslední buňky s daty ve sloupci. Právě jsme použili klíčová slova související se sloupci.
Vyberte sadu dat v aplikaci Excel pomocí VBA
Nyní víme, jak získat poslední řádek a poslední sloupec aplikace Excel pomocí VBA. Díky tomu můžeme snadno vybrat tabulku nebo datovou sadu. Po výběru datové sady nebo tabulky s nimi můžeme provést několik operací, jako je kopírování, vkládání, formátování, mazání atd.
Zde máme sadu dat. Tato data se mohou rozšiřovat směrem dolů. Pouze počáteční buňka je pevná, což je B4. Poslední řádek a sloupec není pevný. Potřebujeme dynamicky vybrat celou tabulku pomocí vba.

Kód VBA pro výběr tabulky s prázdnými buňkami

Sub select_table () Dim last_row, last_col As Long 'Get last row last_row = Cells (Rows.Count, 2). End (xlUp) .Row' Get last column last_col = Cells (4, Columns.Count) .End (xlToLeft) .Column 'Vybrat celou tabulku Rozsah (buňky (4, 2), buňky (last_row, last_col)). Vyberte End Sub

Když to spustíte, celá tabulka bude vybrána ve zlomku sekundy. Můžete přidat nové řádky a sloupce. Vždy vybere celá data.

Výhody této metody:

1. Je to snadné. Doslova jsme napsali pouze jeden řádek, abychom získali poslední řádek s daty. Díky tomu je to snadné.
2. Rychle. Méně řádku kódu, méně času.
3. Snadno pochopitelné.
4. Funguje perfektně, pokud máte neobratnou datovou tabulku s pevným počátečním bodem.

Nevýhody metody Range.End:

1. Výchozí bod musí být znát.
2. Můžete získat pouze poslední neprázdnou buňku ve známém řádku nebo sloupci. Když váš výchozí bod není pevný, bude k ničemu. Což je velmi nepravděpodobné.

2. Najděte poslední řádek pomocí funkce Najít ()
Nejprve se podívejme na kód.

 Sub last_row () lastRow = ActiveSheet.Cells.Find ("*", searchorder: = xlByRows, searchdirection: = xlPrevious) .Row Debug.Print lastRow End Sub 

Jak vidíte na obrázku, tento kód přesně vrací poslední řádek.
Jak to funguje?
Zde používáme funkci find k nalezení jakékoli buňky, která obsahuje cokoli, pomocí operátoru zástupných znaků „*“. Hvězdička slouží k nalezení čehokoli, textu nebo čísla.

Pořadí vyhledávání nastavujeme podle řádků (pořadí hledání: = xlByRows). Také sdělíme Excelu vba směr hledání jako xlPrevious (searchdirection: = xlPrevious). Umožňuje najít funkci pro hledání od konce listu po řádcích. Jakmile najde buňku, která obsahuje cokoli, zastaví se. K načtení posledního řádku z aktivní buňky používáme metodu Range.Row.

Výhody funkce Najít pro získání poslední buňky s daty:

1. Nemusíte znát výchozí bod. Jen se dostanete do poslední řady.
2. Může být obecný a lze jej použít k vyhledání poslední buňky s daty v libovolném listu bez jakýchkoli změn.
3. Lze použít k vyhledání poslední instance konkrétního textu nebo čísla na listu.

Nevýhody funkce Find ():

1. Je to ošklivé Příliš mnoho argumentů.
2. Je to pomalé.
3. Nelze použít k získání posledního neprázdného sloupce. Technicky můžete. Ale jde to příliš pomalu.

3. Pomocí funkce SpecialCells získáte poslední řádek
Funkce SpecialCells s argumentem xlCellTypeLastCell vrací poslední použitou buňku. Podívejme se nejprve na kód

Sub spl_last_row_ () lastRow = ActiveSheet.Cells.SpecialCells (xlCellTypeLastCell).Row Debug.Print lastRow End Sub

Pokud spustíte výše uvedený kód, získáte číslo řádku poslední použité buňky.

Jak to funguje?

Toto je ekvivalent vba zkratky CTRL+End v Excelu. Vybírá naposledy použitou buňku. Pokud zaznamenáte makro při stisknutí kláves CTRL+End, získáte tento kód.

Sub Macro1 () '' Macro1 Macro '' ActiveCell.SpecialCells (xlLastCell). Vyberte End Sub 

Právě jsme jej použili k získání čísla posledního použitého řádku buňky.

Poznámka: Jak jsem řekl výše, tato metoda vrátí poslední použitou buňku, nikoli poslední buňku s daty. Pokud odstraníte data v poslední buňce, výše uvedený kód vba vrátí stále stejný odkaz na buňky, protože to byla „naposledy použitá buňka“. Nejprve musíte dokument uložit, abyste získali poslední buňku s daty pomocí této metody.

Odstraňte listy bez výzev k potvrzení pomocí VBA v aplikaci Microsoft Excel

Přidat a uložit nový sešit pomocí jazyka VBA v aplikaci Microsoft Excel 2016

Zobrazit zprávu na stavovém řádku aplikace Excel VBA

Vypněte varovné zprávy pomocí VBA v aplikaci Microsoft Excel 2016

Oblíbené články:

Funkce VLOOKUP v Excelu

COUNTIF v aplikaci Excel 2016

Jak používat funkci SUMIF v aplikaci Excel