Znalezienie ostatnio używanego wiersza i kolumny jest jednym z podstawowych i ważnych zadań każdej automatyzacji w programie Excel przy użyciu VBA. W przypadku automatycznego kompilowania arkuszy, skoroszytów i rozmieszczania danych wymagane jest znalezienie limitu danych w arkuszach.
W tym artykule wyjaśnimy każdą metodę znajdowania ostatniego wiersza i kolumny w programie Excel w najprostszy sposób.
1. Znajdź ostatni niepusty wiersz w kolumnie za pomocą Range.End
Zobaczmy najpierw kod. Wyjaśnię to listownie.
Sub getLastUsedRow() Dim last_row As Integer last_row = Cells(Rows.Count, 1)).End(xlUp).Row „Ta linia pobiera ostatni wiersz Debug.Print last_row End Sub
Powyższa podrzędna odnajduje ostatni wiersz w kolumnie 1.
Jak to działa?
To tak, jakby przejść do ostatniego wiersza w arkuszu, a następnie nacisnąć skrót CTRL+UP.
Komórki (liczba wierszy, 1): Ta część wybiera komórkę w kolumnie A. Rows.Count daje 1048576, co zwykle jest ostatnim wierszem w arkuszu Excela.
Komórki(1048576, 1)
.Koniec (xlUp): End to metoda klasy zakresu, która służy do nawigowania w arkuszach do końców. xlUp to zmienna określająca kierunek. Razem to polecenie wybiera ostatni wiersz z danymi.
Komórki(Wiersze.Liczba, 1).End(xlUp)
.Wiersz : wiersz zwraca numer wiersza wybranej komórki. Stąd otrzymujemy numer wiersza ostatniej komórki z danymi w kolumnie A. w naszym przykładzie jest to 8.
Zobacz, jak łatwo znaleźć ostatnie wiersze z danymi. Ta metoda wybierze ostatni wiersz w danych niezależnie od pustych komórek przed nim. Widać to na obrazku, że tylko komórka A8 zawiera dane. Wszystkie poprzednie komórki są puste z wyjątkiem A4.
Wybierz ostatnią komórkę z danymi w kolumnie
Jeśli chcesz zaznaczyć ostatnią komórkę w kolumnie A, po prostu usuń „.row” z końca i napisz .select.
Sub getLastUsedRow() Cells(Rows.Count, 1)).End(xlUp).Select ‘Ta linia wybiera ostatnią komórkę w kolumnie End Sub
Polecenie „.Wybierz” wybiera aktywną komórkę.
Pobierz kolumnę adresu ostatniej komórki
Jeśli chcesz uzyskać adres ostatniej komórki w kolumnie A, po prostu usuń „.row” z końca i wpisz .address.
Sub getLastUsedRow() add=Cells(Rows.Count, 1)).End(xlUp).address „Ta linia wybiera ostatnią komórkę w kolumnie Debug.print add End Sub
Zakres.Adres funkcja zwraca adres aktywnej komórki.
Znajdź ostatnią niepustą kolumnę w rzędzie
To prawie to samo, co znalezienie ostatniej niepustej komórki w kolumnie. Tutaj otrzymujemy numer kolumny ostatniej komórki z danymi w wierszu 4.
Sub getLastUsedCol() Dim last_col As Integer last_col = Cells(4,Columns.Count).End(xlToLeft).Column „Ten wiersz pobiera ostatnią kolumnę Debug.Print last_col End Sub
Na obrazku widać, że zwraca numer kolumny ostatniej niepustej komórki w wierszu 4. Czyli 4.
Jak to działa?
Cóż, mechanika jest taka sama, jak znalezienie ostatniej komórki z danymi w kolumnie. Właśnie użyliśmy słów kluczowych związanych z kolumnami.
Wybierz zestaw danych w Excelu za pomocą VBA
Teraz wiemy, jak uzyskać ostatni wiersz i ostatnią kolumnę programu Excel za pomocą VBA. Korzystając z tego możemy łatwo wybrać tabelę lub zbiór danych. Po wybraniu zestawu danych lub tabeli możemy wykonać na nich kilka operacji, takich jak kopiuj-wklej, formatowanie, usuwanie itp.
Tutaj mamy zestaw danych. Te dane mogą rozszerzać się w dół. Tylko komórka początkowa jest ustalona, czyli B4. Ostatni wiersz i kolumna nie są ustalone. Musimy dynamicznie zaznaczyć całą tabelę za pomocą vba.
Kod VBA do wyboru tabeli z pustymi komórkami
Sub select_table() Dim last_row, last_col As Long 'Pobierz ostatni wiersz last_row = Cells(Rows.Count, 2).End(xlUp).Row 'Pobierz ostatnią kolumnę last_col = Cells(4, Columns.Count).End(xlToLeft) .Column 'Zaznacz całą tabelę Range(Cells(4,2), Cells(last_row, last_col)).Select End Sub
Kiedy to uruchomisz, cała tabela zostanie wybrana w ułamku sekundy. Możesz dodać nowe wiersze i kolumny. Zawsze wybiera całe dane.
Korzyści z tej metody:
- To jest łatwe. Dosłownie napisaliśmy tylko jedną linię, aby uzyskać ostatni wiersz z danymi. To ułatwia.
- Szybko. Mniej linii kodu, mniej czasu.
- Łatwy do zrozumienia.
- Działa doskonale, jeśli masz niezdarną tabelę danych ze stałym punktem początkowym.
Wady metody Range.End:
- Punkt wyjścia musi być znany.
- Możesz uzyskać tylko ostatnią niepustą komórkę w znanym wierszu lub kolumnie. Jeśli twój punkt początkowy nie jest ustalony, będzie bezużyteczny. Co jest bardzo mniej prawdopodobne.
2. Znajdź ostatni wiersz za pomocą funkcji Find()
Zobaczmy najpierw kod.
Sub last_row() lastRow = ActiveSheet.Cells.Find("*", kolejność wyszukiwania:=xlByRows, searchdirection:=xlPrevious).Row Debug.Print lastRow End Sub
Jak widać na obrazku, ten kod dokładnie zwraca ostatni wiersz.
Jak to działa?
Tutaj używamy funkcji find, aby znaleźć dowolną komórkę, która zawiera cokolwiek za pomocą operatora wieloznacznego "*". Gwiazdka służy do znajdowania czegokolwiek, tekstu lub liczby.
Ustawiamy kolejność wyszukiwania według wierszy (searchorder:=xlByRows). Mówimy również excelowi vba o kierunku wyszukiwania jako xlPrevious (searchdirection:=xlPrevious). To sprawia, że funkcja find do wyszukiwania od końca arkusza, mądrze. Gdy znajdzie komórkę, która zawiera cokolwiek, zatrzymuje się. Używamy metody Range.Row, aby pobrać ostatni wiersz z aktywnej komórki.
Korzyści z funkcji Znajdź do pobrania ostatniej komórki z danymi:
- Nie musisz znać punktu wyjścia. Po prostu dostajesz ostatni rząd.
- Może być ogólny i może być używany do znajdowania ostatniej komórki z danymi w dowolnym arkuszu bez żadnych zmian.
- Może służyć do znajdowania dowolnego ostatniego wystąpienia określonego tekstu lub numeru na arkuszu.
Wady funkcji Find():
- To jest brzydkie. Zbyt wiele argumentów.
- To jest wolne.
- Nie można użyć, aby uzyskać ostatnią niepustą kolumnę. Technicznie możesz. Ale robi się zbyt wolno.
3. Korzystanie z funkcji SpecialCells, aby uzyskać ostatni wiersz
Funkcja SpecialCells z argumentem xlCellTypeLastCell zwraca ostatnio używaną komórkę. Zobaczmy najpierw kod
Sub spl_last_row_() lastRow = ActiveSheet.Cells.SpecialCells (xlCellTypeLastCell).Row Debug.Print lastRow End Sub
Jeśli uruchomisz powyższy kod, otrzymasz numer wiersza ostatnio używanej komórki.
Jak to działa?
Jest to odpowiednik skrótu CTRL+End w vba w programie Excel. Wybiera ostatnio używaną komórkę. Jeśli nagrasz makro, naciskając CTRL+End, otrzymasz ten kod.
Sub Makro1() ' ' Makro1 ' ' ActiveCell.SpecialCells(xlLastCell).Select End Sub
Po prostu użyliśmy go, aby uzyskać numer wiersza ostatnio używanej komórki.
Notatka: Jak powiedziałem powyżej, ta metoda zwróci ostatnią używaną komórkę, a nie ostatnią komórkę z danymi. Jeśli usuniesz dane w ostatniej komórce, powyższy kod vba nadal zwróci to samo odwołanie do komórek, ponieważ była to „ostatnia używana komórka”. Musisz najpierw zapisać dokument, aby uzyskać ostatnią komórkę z danymi przy użyciu tej metody.
Usuń arkusze bez monitów o potwierdzenie za pomocą VBA w programie Microsoft Excel
Dodaj i zapisz nowy skoroszyt za pomocą VBA w programie Microsoft Excel 2016
Wyświetl komunikat na pasku stanu Excel VBA
Wyłącz komunikaty ostrzegawcze za pomocą VBA w programie Microsoft Excel 2016
Popularne artykuły:
Funkcja WYSZUKAJ.PIONOWO w programie Excel
LICZ.JEŻELI w Excelu 2016
Jak korzystać z funkcji SUMIF w programie Excel?
