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í

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

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 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.

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

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ě…

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

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.

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

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.

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

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.

1
2
3
4
5
6
7
8
9

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)?

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

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

Uložit článek jako PDF