Odstranění řádků (na základě hodnoty)

Odstranění řádků (na základě hodnoty) patří k častým dotazům v diskusích. Úlohu je třeba rozdělit na dvě části – nalezení požadovaných buněk a následné odmazání řádků, na kterých leží.

Výběr buněk lze provádět několika způsoby – filtrem, s pomocí dialogů Najít a nahradit (CTRL + F) či Přejít na/Jinak (F5, CTR + G, tlačítko Jinak), resp. karta Domů / skupina Úpravy, Najít a vybrat.

Příklad 1 - prázdné buňky jako kritérium

Příklad 1 – prázdné buňky jako kritérium

Filtrování

Automatický filtr

Automatický filtr


Tip: Ve vlastním filtru je možné používat zástupné symboly * a ?.

* … žádný, jeden nebo více znaků
? … jeden libovolný znak

P* … textový řetězec začínající písmenem P
*olo* … textový řetězec obsahující „olo“ (na začátku, uprostřed, na konci)
????????? … devítiznakový řetězec

Dialog Najít a nahradit

Dialog Najít a nahradit / Najít vše / CTRL + A

Dialog Najít a nahradit / Najít vše / CTRL + A

Podstatné je ve výsledcích vyhledávání (tlačítko Najít vše) vybrat všechny položky ve výpisu (CTRL + A). Tím dojde k vyznačení buněk i na listu.

Tip: I v dialogu Najít a nahradit je možné používat zástupné symboly * a ?. Kromě toho zvládá vyhledávat i podle formátu (rozklepněte tlačítko Možnosti). Tímto způsobem mimochodem dokážete vybrat i odemčené buňky.

Dialog Přejít na – jinak

Dialog Přejít na - jinak

Dialog Přejít na – jinak

Dialog Přejít na – jinak vhodně doplňuje vyhledávání. Jeho schopnosti ve VBA představuje metoda SpecialCells, jak si ukážeme později.

Jsou-li vybrány buňky a je potřeba odstranit celé řádky, stačí v kontextovém menu vybrat Odstranit / Celý řádek.

Volba Odstranit / Celý řádek

Volba Odstranit / Celý řádek

Pozn. Je škoda, že nefunguje klávesová zkratka SHIFT + mezerník. Ta vybírá celý řádek jen pro aktivní buňku.

Odstranění řádků ve VBA

Příklad 1 - prázdné buňky jako kritérium

Příklad 1 – prázdné buňky jako kritérium

První ukázky kódu prochází oblast položku po položce shora dolů. Testují, zda-li je buňka prázdná (IsEmpty) a pokud ano, odmazávají celý řádek (EntireRow.Delete). A obě jsou špatně…

Sub OdstraneniRadkuA()

    Dim rngOblast As Range
    Dim rngBunka As Range

    For Each rngBunka In Range("B2:B7")
       
        If IsEmpty(rngBunka) Then

            rngBunka.EntireRow.Delete

        End If

    Next rngBunka

End Sub
Sub OdstraneniRadkuB()

    Dim rngOblast As Range
    Dim rngBunka As Range
   
    Set rngOblast = Range("B2:B7")

    For Each rngBunka In rngOblast
       
        If IsEmpty(rngBunka) Then

            rngBunka.EntireRow.Delete

        End If

    Next rngBunka

End Sub

Procedury jsou si velmi podobné a obsahují programátorskou tfujtajblovost. Neplatí zde pravidlo, že při průchodu cyklem nesmí programátor zasahovat do čítače, resp. jeho mezí (odstranění řádku mění prohledávanou oblast). Pokud tak činíte, pak jen ve zvláštních případech a při velmi dobře promyšleném algoritmu.

Předkládám ještě jednu proceduru prohledávání shora dolů. Tentokrát si ji odkrokujte (okna vedle sebe, F8). I z tohoto důvodu je zde řádek rngBunka.Select.

Sub OdstraneniRadkuC()

    Dim rngOblast As Range
    Dim rngBunka As Range
   
    Dim i As Integer
    Dim intPocet As Integer

    Set rngOblast = Range("B2:B7")
   
    intPocet = rngOblast.Cells.Count

    For i = 1 To intPocet

        Set rngBunka = rngOblast.Cells(i)
       
        rngBunka.Select
       
        If IsEmpty(rngBunka) Then

            rngBunka.EntireRow.Delete

        End If

    Next i

End Sub

Kód zde sice neobsahuje chybu uvedenou výše, ale přeci jen je postaven na prapodivném základu. Zatímco čítač hodnoty navyšuje, oblast se zmenšuje… Je to jako řídit auto v protisměru. Zapomeňte i na ni.

Odstraňování řádků je dost možná jediný případ, kdy je vhodné cyklus obrátit a procházet položky od konce. Doporučuji kód odkrokovat.

Sub OdstraneniRadkuD()

    Dim rngOblast As Range
    Dim rngBunka As Range
   
    Dim i As Integer
    Dim intPocet As Integer

    Set rngOblast = Range("B2:B7")
   
    intPocet = rngOblast.Cells.Count

    For i = intPocet To 1 Step -1

        Set rngBunka = rngOblast.Cells(i)
       
        rngBunka.Select
       
        If IsEmpty(rngBunka) Then

            rngBunka.EntireRow.Delete

        End If

    Next i

End Sub

Jak bylo napsáno, dialog Přejít na – jinak ve pod VBA zastoupen metodou SpecialCells. V případě prázdných buněk tak můžeme vypustit cyklus a kód se smrskne do jednoho řádku.

Sub OdstraneniRadkuE()

    Dim rngOblast As Range

    Set rngOblast = Range("B2:B7")
   
    rngOblast.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub

A jak to udělat v případě, že kritériem pro odstranění řádků nebudou prázdné buňky, ale konkrétní hodnota (hodnoty)?

Příklad 2

Příklad 2

Podle obrázku je naším cílem vymazat řádky s hodnotami začínajícími písmenem X. Vrátíme se k dialogu Najít a nahradit. Pod VBA není bohužel možné nasimulovat chování Najít vše / CTRL + A. Nicméně jeho prostřednictvím si připravíme půdu pro SpecialCells.

Sub OdstraneniRadkuF()

    Dim rngOblast As Range

    Set rngOblast = Range("B2:B7")

    With rngOblast

        .Replace What:="X*", Replacement:="=NA()", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False

        .SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete

    End With

End Sub

Uvedené makro nahrazuje položky začínající písmenem X za vzorec =NA(), což se v českém prostředí na listu objeví jako =NEDEF(). Výsledek této funkce (chybová hodnota #NENÍ K DISPOZICI často využívaná v grafech) následně zpracuje metoda SpecialCells. Snímek níže ukazuje, jak by vypadal výběr chybových hodnot ručně.

Dialog Přejít na - jinak (chybové hodnoty)

Dialog Přejít na – jinak (chybové hodnoty)

Příloha
odstraneni_radku.zip

Klikni a stahuj!