První prázdná buňka zdola

Úvodní stránka | Blog | Excel VBA | 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.

    1
    2
    3
    4
    5
    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ě:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    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:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    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
    Uložit článek jako PDF