První prázdná buňka zdola

Téma vyhledávání první prázdné buňky zdola ve sloupci je natolik frekventované, že jej znovu otevírám tímto článkem.

Máte ve sloupci výpis hodnot a další potřebujete přidat pod poslední vyplněnou buňku.

První prázdná buňka zdola - zadání

První prázdná buňka zdola – zadání

Chytřejší kolega vám pošle níže uvedený kus kódu.

Sub VyberPrvniPrazdnouBunkuZdola()

    Cells(65536, 1).End(xlUp).Offset(1, 0).Select

End Sub

Procedura neřeší naše zadání přímo, staví se k problému z opačné strany. Od buňky ležící v průsečíku řádku 65536 a prvního sloupce postupuje směrem vzhůru (xlUp, běžně CTRL+šipka nahoru), dokud nenarazí na první vyplněnou buňku. Od ní se poté posune o jeden řádek dolů. Číslo 65536 by mělo vyjadřovat poslední řádek listu. To ovšem platilo v dobách Excelu 2003 a starších binárních souborech (xls). Dnešní listy sešitů (xlsx) mají řádků mnohem více (stačí podržet stisknuté tlačítko myši na místě, kde se potkává záhlaví řádků a sloupců a podívat se do Pole názvů) a je proto vhodné namísto této konstanty uvádět dynamicky Rows.Count. Další zpravidla neřešenou, ale podstatnou skutečností je to, že procedura chybně označí (jako první prázdnou) buňku z druhého řádku, pokud je sloupec celý prázdný (nemá hlavičku). Proč? Excel (VBA) má při použití .End(xlUp) nebo třeba při výběru využité oblasti listu (UsedRange) tendenci vždy vybrat alespoň jednu buňku listu (A1), a to i za cenu, že je prázdná, resp. do ní uživatel nikdy nezasáhl (nový list). Kód bych tedy upravil následovně:

Sub PrvniPrazdna()

    Dim intSloupec As Integer
    Dim rngTemp As Range
    Dim strPrvniPrazdna As String
   
    'zpracovávaný sloupec
    intSloupec = 1

    'první neprázdná buňka zdola
    'nefunkční pro celý prázdný sloupec
    'strPrvniPrazdna = Cells(Rows.Count, intSloupec).End(xlUp).Offset(1, _
        0).Address(0, 0)

    'úprava
    Set rngTemp = Cells(Rows.Count, intSloupec).End(xlUp)

    'je buňka prázdná?
    If IsEmpty(rngTemp) Then
        'ano (prázdná buňka A1)
        strPrvniPrazdna = rngTemp.Address(0, 0)
    Else
        'ne, posun o jednu níže
        strPrvniPrazdna = rngTemp.Offset(1, 0).Address(0, 0)
    End If

    '.End(xlUp) zastaví:
    'prázdný řetezec,
    'prázdný řetězec převedený na hodnotu,
    'hodnota skrytá formátem (;;;),
    'nula skrývaná přes Možnosti / ...,
    'apostrof
   
    '.End(xlUp) nezastaví:
    'skrytá hodnota (řádek) ať už přímo nebo filtrem

End Sub

Pamatujte, že technika .End(xlUp) ve svém hledání přeskakuje přímo či filtrem skryté buňky s hodnotou. Sloupec dat by přirozeně neměl obsahovat žádné kulišárny typ děr (prázdných buněk) mezi hodnotami atp. Pokud se na to můžete spolehnout, je možné si v úloze pomoci funkcemi listu POČET, případně POČET2 (zohledněte ve výsledku případnou hlavičku). Pro úplnost uvádím ještě jeden postup, který vychází z maticového vzorce listu

{=MAX(NE(JE.PRÁZDNÉ(Oblast))*ŘÁDEK(Oblast))}.

Jeho cílem je rovněž nalezení řádku poslední neprázdné buňky v oblasti. Aplikování pod VBA ve vlastní funkci (UDF) vypadá takto:

Sub Test()

    Dim intSloupec As Integer
    Dim strPrvniPrazdna As String

    'zpracovávaný sloupec
    intSloupec = 1

    strPrvniPrazdna = epfSLOUPECPRVNIPRAZDNA(intSloupec)

End Sub

Public Function epfSLOUPECPRVNIPRAZDNA(ByVal Sloupec As Variant)

    Dim strAdresa As String

    'vynucený přepočet funkce
    Application.Volatile True

    With ActiveSheet.UsedRange
        strAdresa = Replace(Columns(Sloupec).Address(0, 0), ":", _
            "1:") & .Cells(.Cells.Count).Row
    End With

    epfSLOUPECPRVNIPRAZDNA = Cells(Evaluate("MAX(NOT(ISBLANK(" & _
        strAdresa & "))*ROW(" & strAdresa & "))") + 1, _
        Columns(Sloupec).Column).Address(0, 0)
       
    'prohledávání zastaví:
    'skrytá hodnota (řádek) ať už přímo nebo filtrem
    'prázdný řetezec,
    'prázdný řetězec převedený na hodnotu,
    'hodnota skrytá formátem (;;;),
    'nula skrývaná přes Možnosti / ...,
    'apostrof

End Function
První prázdná buňka zdola - vlastní funkce

První prázdná buňka zdola – vlastní funkce

Klikni a stahuj!