fbpx

Usuwanie pustych wierszy w zakresie za pomocą makra (VBA)

26.02.2015 | ECP3, Makra VBA

Pomysł na ten artykuł podsunął mi jeden z czytelników mojego blogu – Szafran (już po raz drugi korzystam z pomysłu Szafrana – pierwszy wpis znajdziecie tutaj). Dzięki!

Załóżmy, że analizujemy sprzedaż produktów w konkretnych dniach. Docelowo chcemy stworzyć wykres obrazujący sprzedaż. Dane są w postaci: data, wynik sprzedaży (Szafran akurat miał inne dane, ale schemat był dokładnie taki sam). W weekendy nasza firma nie pracuje, ale dane dostajemy w takiej postaci:

Dane wejściowe

Dane wejściowe

Jak widać, wypisane są wszystkie daty z określonego zakresu, niezależnie od tego, czy danego dnia coś sprzedano. Jest to kiepskie rozwiązanie, zwłaszcza gdy chcemy zrobić np. wykres tej sprzedaży. Wyglądałby on tak:

MalinowyExcel-Usuwanie-pustych-wierszy-wykres-przed

Wykres utworzony z danych zawierających puste komórki

Mistrzostwo świata to to nie jest, przyznajcie sami. Widywałam lepsze wykresy…  Koniecznie trzeba usunąć puste wiersze. Można to zrobić ręcznie, co wystarczyłoby pewnie w przypadku małej liczby danych. Gdy jednak mamy do czynienia z większą tabelą, aby ułatwić sobie pracę, koniecznie trzeba napisać makro. W tym artykule opiszę właśnie takie makro – usunie ono puste wiersze z zakresu.

Mamy arkusz, w którym jest tabela z danymi. U mnie są one w zakresie A1:C15. Dla makra nie będzie to jednak miało znaczenia. Najważniejsze jest, aby przed uruchomieniem makra zaznaczyć jedną z komórek tego zakresu. Na tej podstawie bowiem wybierzemy zakres, z którego automat usunie wiersze.

Wstawianie modułu

Przejdźmy zatem do pisania makra. Tak, tak – pisania. Nic tutaj nie będziemy nagrywać. Najpierw otwórzmy edytor Visual Basic (VBE). Można to zrobić, wybierając ze wstążki Deweloper/ Visual Basic (jeśli nie masz na wstążce karty Deweloper, koniecznie ją dodaj – tutaj opisałam, jak to zrobić).

Pojawi się okno VBE, a w nim okienko VBAProject:

MalinowyExcel-Usuwanie-pustych-wierszy-VBAProject

Okienko VBAProject

Wylistowane są w nim wszystkie używane skoroszyty (niekoniecznie widoczne!). Na pewno na waszych komputerach lista będzie inna, ale nie ma to znaczenia. Najważniejsze, aby na tej liście był plik, w którym tworzymy makro. U mnie jest to UsuńPuste.xlsx (na razie jeszcze rozszerzenie to xlsx).

Teraz wstawimy moduł. Moduł to miejsce, gdzie przechowywany jest kod VBA, czyli nasze makro. Aby go dodać, kliknij prawym przyciskiem myszy np. na  pozycji Arkusz1 (Arkusz1) (zaznaczone na obrazku). Następnie z menu kontekstowego wybierz Instert/ Module. Po prawej stronie pojawi się okno tego modułu, które jest na razie puste. W tym właśnie oknie będziemy pisać nasze makro. Dla porządku można jeszcze nazwać moduł. Nie jest to konieczne, bo w tym skoroszycie będzie tylko jeden moduł.

Kod makra

W oknie modułu wpiszmy następujący kod:

Sub Usun_puste_wiersze()
  Dim Zakres As Range, Kolumna As Range, Komorka As Range
  Dim Licznik As Long, LiczbaKomorek As Long, Usunietych As Long

  'UWAGA! Zaznacz dowolną komórkę w zakresie, który ma zostać oczyszczony
  Set Zakres = ActiveCell.CurrentRegion
  Set Kolumna = Zakres.Columns(3)
  LiczbaKomorek = Zakres.Rows.Count

  Usunietych = 0
  For Licznik = 1 To LiczbaKomorek
     Set Komorka = Kolumna.Cells(Licznik - Usunietych, 1)
     If Komorka.Value = 0 Then
        Rows(Licznik - Usunietych).Delete
        Usunietych = Usunietych + 1
     End If
  Next Licznik

  Set Zakres = Nothing
  Set Kolumna = Nothing
  Set Komorka = Nothing
End Sub

Zauważmy, że to makro jest procedurą, zatem jego nazwę poprzedzamy słówkiem sub. Na początku  kodu znajduje się deklaracja zmiennych: obiektowych (Zakres, Kolumna, Komorka) i liczbowych (Licznik, LiczbaKomorek, Usunietych).

Zakres to po prostu nasze dane. Jest ustalany na podstawie zaznaczonej komórki, dlatego – tak jak pisałam na początku – przed uruchomieniem makra należy zaznaczyć dowolną komórkę zakresu.

Kolumna to trzecia kolumna naszego zakresu. To właśnie w niej będziemy szukać pustych komórek.

LiczbaKomorek to zmienna mówiąca o liczbie wierszy w Zakresie i w Kolumnie (jest identyczna).

Następnie zaczyna się pętla For. Zadziała tyle razy, ile jest wierszy w Zakresie. Po kolei sprawdzana jest każda komórka w Kolumnie. Jeśli jej wartość jest równa zero (0), czyli jeśli jest pusta, wiersz jest usuwany, a zmienna Usunietych zwiększa się o 1. Zmienna ta jest zmienną roboczą. Jej wartość bowiem odejmujemy od zmiennej Licznik. Ta z kolei zmienna mówi o numerze wiersza komórki, którą sprawdzamy, a następnie usuwamy.

Na samym końcu czyścimy zmienne obiektowe. I wszystko! Krótkie proste makro, a ile pracy zaoszczędzonej? Oczywiście, jak to zwykle w programowaniu bywa, przedstawione rozwiązanie nie jest jedynym możliwym. Znam jeszcze co najmniej 2, które również zadziałają. A może wy macie jakieś pomysły? Jeśli tak – napiszcie w komentarzach. Jestem ciekawa, co wymyśliliście. 🙂

Zapisanie pliku

Pamiętaj, że działania makra nie można cofnąć, polecam zatem zawsze zapisać plik przed uruchomieniem makra.

Na razie nasz plik ma rozszerzenie .xlsx. W plikach z takim rozszerzeniem nie można przechowywać makr. Musimy więc zapisać plik jako Skoroszyt programu Excel z obsługa makr (*.xlsm). Można to zrobić, korzystając z opcji Zapisz jako lub po prostu zapisać plik za pomocą skrótu klawiszowego Ctrl + s. Excel wykryje, że w skoroszycie znajdują się makra i wyświetli komunikat: W skoroszytach bez obsługi makr nie można zapisywać następujących funkcji: Projekt VBA.

Komunikat o makrach w skoroszycie: W skoroszytach bez obsługi makr nie można zapisywać następujących funkcji: Projekt VBA

Komunikat o makrach w skoroszycie

Aby zapisać plik tak jak należy, czyli .xlsm, trzeba wybrać Nie. Jest to trochę mylące, bo domyślnym przyciskiem jest Tak i myszka większości użytkowników od razu tam wędruje. 🙂

Następnie z pola Zapisz jako typ wystarczy wybrać typ pliku Skoroszyt programu Excel z obsługa makr (*.xlsm). Jeśli twój Windows jest ustawiony tak, by nie wyświetlać rozszerzeń znanych typów plików, zobaczysz wtedy następujący typ na liście: Skoroszyt programu Excel z obsługa makr. W obu przypadkach uzyskamy pożądany efekt.

Uruchamianie makra

Ok, plik zapisany, makro napisane. Teraz trzeba je uruchomić. Z poziomu kodu najprostszym sposobem uruchomienia makra jest ustawienie kursora w dowolnym miejscu kodu i użycie klawisza F5. Mimo że widok nie przełączy się na arkusz Excela, to makro zostanie wykonane.

Innym sposobem jest naciśnięcie zielonej strzałki w prawo. Znajduje się ona na pasku narzędzi Standard:

Uruchamianie makra za pomocą zielonej strzałki na pasku narzędzi

Uruchamianie makra za pomocą zielonej strzałki na pasku narzędzi

Z poziomu arkusza makro można uruchomić z menu Deweloper/ Kod/ Makra, przez wybranie konkretnego makra z okna Makro. Skrót klawiszowy wywołujący to okno to Alt + F8.

A sposób bardziej user-friendly opisuję w kolejnym artykule: Uruchamianie makra za pomocą przycisku w arkuszu.

Wynik działania makra

Ok. Niezależnie od tego, z której metody uruchamiania makra skorzystamy, jego działanie będzie identyczne. Da w wyniku taki efekt:

Wynik działania makra

Wynik działania makra

Jak widać, wiersze z pustymi komórkami zostały usunięte i o to chodziło.

Wracając do wykresu, który chcieliśmy utworzyć z tych danych, to teraz wygląda on tak (trochę go ztuningowałam:)):

Wykres z danych oczyszczonych z pustych komórek

Wykres z danych oczyszczonych z pustych komórek

Pokusiłam się jeszcze o stworzenie wykresu danych narastająco 🙂

Wykres z danych oczyszczonych z pustych komórek - narastająco

Wykres z danych oczyszczonych z pustych komórek – narastająco

Voila! Wszystko!

Wow. Całkiem długi artykuł wyszedł. Mam nadzieję, że wam się przyda! A może nie tylko wam? Jeśli znacie kogoś, kto może skorzystać z tego makra, wyślijcie mu, proszę, link do tego artykułu: https://malinowyexcel.pl/usuwanie-pustych-wierszy-w-zakresie-za-pomoca-makra-vba. Niech też sobie uprości życie!

 

 

Redakcja językowa: Aleksandra Wasiak

Jeżeli chcesz lepiej poznać makra i VBA zapisz się na listę zainteresowanych kursem Excel w codziennej pracy cz. 3! Podczas oczekiwania na kurs będę Ci wysyłała informacje, ciekawostki i excelowe triki. Bezpłatnie! 

Pomogłam Ci? Postaw mi wirtualną kawę! →

Przeczytaj podobne wpisy

Kategorie

9 komentarzy

  1. Znalazłem tę stronę o parę dni za późno 🙂 W zeszłym tygodniu męczyłem się nad identycznym makrem kasującym puste wiersze, szukałem porad w internecie i żadna jakoś nie za bardzo chciała zadziałać… W końcu sam pokombinowałem i zrobiłem to trochę na około: makro w pierwszym wierszu włącza filtr, w pierwszej kolumnie szuka pustych komórek, następnie kasuje wszystkie wiersze (ale koniecznie z select visible cells), robi potem to samo dla drugiej kolumny i następnie wyłącza filtr. Ale przetestuję też to rozwiązanie z bloga 🙂
    Pozdrawiam!

    Reply
    • Hehe, super! Fajnie, że wymyśliłeś też swoje rozwiązanie.

      Pozdrawiam!

      Reply
  2. ja akurat robię to bez zmiennej pomocniczej określającej liczbę usuniętych wierszy: po prostu pętle zaczynam od ostatniego wiersza czyli:
    For Licznik = LiczbaKomorek To 1 Step -1

    Reply
    • Pewnie – super metoda 🙂

      Reply
  3. A ja to robię bez VBA.
    1. dodaję tymczasowo pomocniczą kolumnę i numeruję w niej kolejno wszystkie wiersze interesującego mnie zakresu,
    2. zakładam autofiltr na całość,
    3. sortuję alfabetycznie/wartościami wg kolumny zawierającej niechciane puste wiersze i excel wyrzuca je na początek/koniec zakresu w zwartej grupie – usuwam tę grupę wierszy jednym zaznaczeniem
    4. przywracam stary porządek dzięki pomocniczej kolumnie

    Pomocnicza kolumna jest potrzebna oczywiście tylko w części przypadków, jeśli porządek w naszej tabeli jest ważny, a nie ma innego jednoznacznie definiującego go licznika.
    Podsumowując – sortowanie z autofiltra załatwia sprawę pustych komórek 🙂 Choć oczywiście makro jest ciekawe, jak ktoś ma ochotę poćwiczyć VBA.

    Reply
    • Magda – super! Dzięki za komentarz.

      Reply
  4. Dziękuję
    tego akurat szukam zaraz sprawdzę jak to działa ,
    Cieszę się że taakie rzeczy można znaleźć w sieci
    Pozdrawiam
    zen

    Reply
  5. Dzięki za inspirację. 🙂
    Trochę zmieniłem Twoje makro.
    Teraz jest tak: Zaznaczamy DOWOLNY zakres w arkuszu,
    i w tym zakresie jeżeli jeżeli cały wybrany wiersz jest pusty to go usuwa.
    kod:
    Sub Usun_puste_wiersze_w_zakresie()
    'W wybranym zakresie usuwa tylko te wiersze które są całe puste
    Dim Zakres As Range
    Application.ScreenUpdating = False
    Set Zakres = Selection
    wierszy = Zakres.Rows.Count – 1
    kolumn = Zakres.Columns.Count – 1
    w1 = Zakres.Row
    k1 = Zakres.Column
    For i = wierszy + w1 To w1 Step -1
    If WorksheetFunction.CountA(Range(Cells(i, k1), Cells(i, k1 + kolumn))) = 0 Then
    Range(Cells(i, k1), Cells(i, k1 + kolumn)).Select
    Selection.Delete Shift:=xlUp
    End If
    Next i
    Set Zakres = Nothing
    Application.ScreenUpdating = True
    End Sub

    Reply
    • Piotr, co ja bym bez Ciebie zrobiła :). Dziękuję za podzielenie się usprawnieniem! Mam nadzieję, ze przyda się wielu osobom 🙂

      Reply

Leave a Reply to Magda Anuluj pisanie odpowiedzi

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *

Pin It on Pinterest