Ostatni niepusty wiersz jest jednym z najbardziej popularnych tematów 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ń!
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.
1- 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.
|
1 2 3 4 5 |
Function lEndXlUp(ByRef rngZakres As Range) As Long lEndXlUp = rngZakres.Cells(rngZakres.Count).End(xlUp).Row End Function |
2- 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”.
|
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 |
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 |
3 – 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.
|
1 2 3 4 5 |
Function lCountA(ByRef rngZakres As Range) As Long lCountA = WorksheetFunction.CountA(rngZakres) End Function |
4 – 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.
|
1 2 3 4 5 |
Function lCurrentRegion(ByRef rngKomorka As Range) As Long lCurrentRegion = rngKomorka.CurrentRegion.Rows.Count End Function |
5 – 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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 |
6 – 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ę.
|
1 2 3 4 5 |
Function lUsedRange(ByRef sNazwaArkusza As String) As Long lUsedRange = Worksheets(sNazwaArkusza).UsedRange.Rows.Count End Function |
7 – 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.
|
1 2 3 4 5 |
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:
- Liczby.
- Tekst.
- Daty.
- Wartości logiczne PRAWDA/FAŁSZ.
- Błędy.
- Wiersze ukryte filtrem.
- Wiersze ukryte ręcznie.
- Puste wiersze w środku tabeli.
- Pusty ciąg tekstowy jako wynik formuły.
- Arkusz chroniony.
Wyniki Testu
Omówienie Wyników
1 – 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ą.
Rozwiązanie z wykorzystaniem właściwości End(xlUp) jest bardzo popularne z powodu bardzo prostej konstrukcji. Osobiście go nie polecam ze względu na pomijanie komórek ukrytych (zbyt duże ryzyko!).
2 – 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.
3 – 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.
4 – 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.
5 – 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.
6 – 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.
7 – 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.
Film na Vlogu
Plik XLSM
Podsumowanie
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.