Temat wyznaczania ostatniego niepustego wiersza w kolumnie jest jednym z najbardziej popularnych w całym VBA. Sprawa z pozoru wydaje się banalnie prosta. Jak się jednak przekonamy, żadne z siedmiu zaproponowanych rozwiązań nie spisuje się w 100% idealnie.
Potencjalne Problemy
Zastanówmy się najpierw dlaczego temat skutecznego wyznaczania ostatniego niepustego wiersza jest w ogóle taki ważny… Powodów jest kilka.
1 – Nadpisanie Istniejących Danych
Największym zagrożeniem jest ryzyko nadpisania już istniejących danych. Nadpisanie jest równoznaczne z ich utratą.
Zobaczmy screen poniżej.
Gdyby ostatni wiersz został wyliczony na 12 i dopisywanie nowych danych zaczęłoby się od wiersza nr 13 to utracone zostałyby dane dla Osaki i Kairu!
Jeśli niektóre wiersze zostają usunięte (nadpisane), to nasze dalsze obliczenia bazują na niekompletnych danych.
Konsekwencje operowania na niepełnej (błędnej!) informacji mogą być już bardzo poważne i niebezpieczne. Pomyłka w raporcie może wprowadzić w błąd osobę decyzyjną i w efekcie doprowadzić do szkodliwych dla firmy działań! Nieświadomie.
Możemy operować na niekompletnych danych zupełnie o tym nie wiedząc!
2 – Puste Wiersze w Tabeli
Drugi problem nie jest aż tak poważny, natomiast nie wolno go lekceważyć. Dotyczy on ryzyka istnienia pustych wierszy wewnątrz tabeli.
Bazując na przykładzie z największymi miastami świata. Gdyby ostatni niepusty wiersz został wyliczony na 16, wówczas dopisywanie nowych danych zaczęlibyśmy do wiersza 17. To spowodowałoby, że wiersze 15 i 16 pozostałyby puste.
Takiej tabeli nie moglibyśmy np. prawidłowo posortować. Zamiast jednej tabeli mielibyśmy dwa zakresy nieciągłe.
3 – Błędny Zakres Dla Formuł
W VBA, podobnie jak w Excelu, bardzo często dodajemy do tabel kolumny pomocnicze. W ten sposób przetwarzamy dane, aby później pokazać je w raportach i wizualizacjach.
Jeśli nieprawidłowo wyliczymy ostatni niepusty wiersz, formuła zostanie wstawiona do nieprawidłowego zakresu.
Sposoby Na Ostatni Niepusty Wiersz
Istnieje wiele różnych filozofii podejścia do tematu ostatniego niepustego wiersza. W tym artykule zaprezentuję i porównam ze sobą 7 różnych sposobów.
Właściwość End(xlUp) Obiektu Range
Jest to sposób chyba najczęściej spotykany w praktyce. Oznacza on symulację skrótu klawiaturowego Ctrl + ↑ W takiej sytuacji skrót ten przeniesie nas do pierwszej widocznej, niepustej komórki.
Function lEndXlUp(ByRef rngZakres As Range) As Long
lEndXlUp = rngZakres.Cells(rngZakres.Count).End(xlUp).Row
End Function
Metoda Find Obiektu Range
Sposób, który również jest dość często spotykany w praktyce. Polega na wyszukaniu pierwszej niepustej komórki w zakresie. Niby na pozór nic specjalnego, ale cały pic polega na tym, że zaczynamy szukać od ostatniej komórki, zaś komórką bazową jest pierwsza z zakresu.
Inaczej mówiąc, jeżeli badamy zakres A1:A20, to komórką startową jest A1, ale pierwszą komórką, którą sprawdzamy jest A20. Czyli mamy do czynienia z czymś co można by było nazwać “zawijaniem arkusza”.
Function lFind(ByRef rngZakres As Range) As Long
Dim lOstStala As Long 'Wiersz z stałą
Dim lOstFormula As Long 'Wiersz z formułą
'Pomiń błędy
1 On Error Resume Next
2 lOstStala = rngZakres.Find(What:="*", _
After:=rngZakres.Cells(1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False, _
SearchFormat:=False).Row
3 lOstFormula = rngZakres.Find(What:="*", _
After:=rngZakres.Cells(1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False, _
SearchFormat:=False).Row
4 On Error GoTo 0
'Wynik jest w tym przypadku wartością maksymalną
5 lFind = WorksheetFunction.Max(lOstStala, lOstFormula)
End Function
Funkcja Arkuszowa ILE.NIEPUSTYCH
Trzeci sposób polega na wykorzystaniu funkcji arkuszowej ILE.NIEPUSTYCH (z ang. COUNTA). W tym przypadku wielkiej filozofii nie ma – po prostu sprawdzamy ile niepustych komórek znajduje się w badanym zakresie.
Function lCountA(ByRef rngZakres As Range) As Long
lCountA = WorksheetFunction.CountA(rngZakres)
End Function
Właściwość CurrentRegion Obiektu Range
Jak sugeruje nazwa, chodzi o obszar bieżący, czyli zakres komórek przylegających w jakiś sposób do siebie.
Jest to odpowiednik skrótu klawiaturowego Ctrl+Shift+8.
Function lCurrentRegion(ByRef rngKomorka As Range) As Long
lCurrentRegion = rngKomorka.CurrentRegion.Rows.Count
End Function
Nietypowe Wykorzystanie Funkcji PODAJ.POZYCJĘ
Ten sposób jest praktycznie nieznany i sprowadza się do bardzo nietypowego ustawienia pierwszego i trzeciego argumentu funkcji PODAJ.POZYCJĘ.
Szukamy możliwie największej liczby i największego tekstu (to nie błąd! – jak wiemy, Excel pozwala sortować tekst). Wiemy jednak, że takich danych w Excelu nie znajdziemy, zatem ustawiając ostatni parametr na 1, zwrócona zostanie pozycja ostatniej liczby i ostatniego tekstu w kolumnie.
Function lMatch(ByRef Zakres As Range) As Long
Dim lTekst As Long
Dim lLiczba As Long
'Sprawdź pozycję ostatniego tekstu i ostatniej liczby
1 On Error Resume Next
2 lTekst = WorksheetFunction.Match("żżż", Zakres, 1)
3 lLiczba = WorksheetFunction.Match(9.99999999999999E+307, Zakres, 1)
4 On Error GoTo 0
' Ostatni wiersz jest w tym przypadku wartością największą
5 lMatch = WorksheetFunction.Max(lTekst, lLiczba)
End Function
Właściwość UsedRange Obiektu Worksheet
Następnym pomysłem znalezienia ostatniego niepustego wiersza jest sprawdzenie obszaru roboczego arkusza.
Ten sposób nie pozwoli nam zatem znaleźć ostatniego niepustego wiersza w pojedynczej kolumnie, tylko w całym arkuszu. Musimy jednak wziąć go również pod uwagę.
Function lUsedRange(ByRef sNazwaArkusza As String) As Long
lUsedRange = Worksheets(sNazwaArkusza).UsedRange.Rows.Count
End Function
Metoda SpecialCells z Parametrem xlLastCell
Ostatnim sposobem jaki sprawdzimy jest odwołanie się do komórek specjalnych i wybór ostatniej z nich.
Jest to symulacja skrótu klawiaturowego F5, czyli przejścia do komórek specjalnych i zaznaczenia opcji Ostatnia komórka.
Function lSpecialCells(ByRef rngZakres As Range) As Long
lSpecialCells = rngZakres.SpecialCells(Type:=xlCellTypeLastCell).Row
End Function
Zasady Testu
Każdy z siedmiu sposobów będę chciał przetestować ze względu na następujące zmienne:
- Test 1 – Rozpoznawanie liczb
- Test 2 – Rozpoznawanie tekstu
- Test 3 – Rozpoznawanie dat
- Test 4 – Rozpoznawanie wartości logicznych
- Test 5 – Rozpoznawanie błędów
- Test 6 – Działanie na wierszach ukrytych filtrem
- Test 7 – Działanie na wierszach ukrytych ręcznie
- Test 8 – Uwzględnianie pustych komórek w tabeli
- Test 9 – Traktowanie formuł zwracających pusty ciąg tekstowy
- Test 10 – Działanie na arkuszu chronionym
Wyniki Testu
Omówienie Wyników
Właściwość End(xlUp) Obiektu Range
Funkcja nie poradziła sobie z danymi ukrytymi (filtr + ukrycie ręczne), ale to oczywiście było do przewidzenia ponieważ jest to symulacja skrótu Ctrl + ↑. Skrót przenosi nas do pierwszej niepustej, widocznej komórki, nie ma możliwości aby zaznaczyć komórkę ukrytą.
To rozwiązanie jest najczęściej stosowane ze względu na łatwą konstrukcję. Osobiście go nie polecam ze względu na pomijanie komórek ukrytych (zbyt duże ryzyko!).
Metoda Find Obiektu Range
Funkcja ta nie poradziła sobie z danymi ukrytymi filtrem. Poradziła sobie natomiast z danymi ukrytymi w sposób ręczny. O dziwo, sposób ukrycia danych ma dla metody Find kluczowe znaczenie. W pozostałych testach funkcja spisała się bezbłędnie.
Funkcja Arkuszowa ILE.NIEPUSTYCH
Funkcja poradziła sobie z komórkami ukrytymi (uwzględnia je w liczeniu), natomiast zwróciła błąd gdy w tabeli znajdował się pusty wiersz. Było to do przewidzenia ponieważ funkcja ILE.NIEPUSTYCH zlicza tylko niepuste komórki, pomija puste.
Właściwość CurrentRegion Obiektu Range
Funkcja również zwróciła błąd w przypadku pustego wiersza w tabeli. Jest to polecenie, które zaznacza wszystkie komórki przyległe do startowej. Oprócz tego nie zadziałała na arkuszu chronionym.
Nietypowe Wykorzystanie Funkcji PODAJ.POZYCJĘ
Funkcja uzyskała nietypowe wyniki, zupełnie inne od pozostałych opcji. Nie poradziła sobie z błędami i wartościami logicznymi. Poradziła sobie natomiast z dwoma najważniejszymi wyzwaniami – pustym wierszem i ukryciem danych.
Właściwość UsedRange Obiektu Worksheet
Ta funkcja jako jedyna przeszła prawidłowo wszystkie testy. Posiada ona jednak pewną wadę, która całkowicie ją dyskwalifikuje. Jaka to wada? Dowiesz się z filmu.
Metoda SpecialCells z Parametrem xlLastCell
Posiada tą samą wadę, co właściwość UsedRange. Nie zadziałała również na arkuszu chronionym i danych ukrytych.
Werdykt
Zwycięzcą testu okazał się wg mnie sposób nr 5, czyli niestandardowe wykorzystanie funkcji PODAJ.POZYCJĘ. Kluczowe jest to, że funkcja radzi sobie z dwoma najważniejszymi wyzwaniami: pustym wierszem w tabeli i ukryciem danych.
Drugie miejsce zajął w moim rankingu sposób nr 2, czyli wykorzystanie metody Find obiektu Range.
Trzecie miejsce przyznaję właściwości End(xlUp) obiektu Range, ale jak wspomniałem – nie rekomenduję tego sposobu, ponieważ całkowicie powija on dane ukryte.
