3 najbolja načina za pronalaženje posljednjeg praznog reda i stupca pomoću VBA-a

Anonim

Pronalaženje posljednjeg korištenog retka i stupca jedan je od osnovnih i važnih zadataka za bilo koju automatizaciju u Excelu pomoću VBA. Za automatsko sastavljanje listova, radnih knjiga i uređivanje podataka morate pronaći ograničenje podataka na listovima.

Ovaj će članak objasniti svaku metodu pronalaženja zadnjeg retka i stupca u Excelu na najjednostavnije načine.
1. Pronađite zadnji redak koji nije prazan u stupcu pomoću Range.End
Pogledajmo prvo kôd. Slovo ću objasniti.

Sub getLastUsedRow () Dim last_row As Integer last_row = Cells (Rows.Count, 1) .End (xlUp) .Row 'Ovaj redak dobiva zadnji redak Debug.Print last_row End Sub

Gornji pododnos nalazi zadnji redak u stupcu 1.

Kako radi?
To je kao da idete na posljednji red u listu, a zatim pritisnete CTRL+UP prečac.
Ćelije (redci, broj, 1): Ovaj dio odabire ćeliju u stupcu A. Redci.Count daje 1048576, što je obično posljednji redak u Excelovom listu.

Stanice (1048576, 1)

.Kraj (xlUp): Kraj je metoda klase raspona koja se koristi za kretanje po listovima do krajeva. xlUp je varijabla koja govori o smjeru. Ova naredba zajedno odabire zadnji redak s podacima.

Ćelije (redaci.broj, 1). Kraj (xlUp)

.Red: row vraća broj retka odabrane ćelije. Stoga dobivamo broj retka posljednje ćelije s podacima u stupcu A. u našem primjeru to je 8.

Pogledajte kako je lako pronaći posljednje retke s podacima. Ova metoda odabire zadnji redak podataka, bez obzira na prazne ćelije prije njega. Na slici možete vidjeti da samo ćelija A8 ima podatke. Sve prethodne ćelije su prazne osim A4.

Odaberite posljednju ćeliju s podacima u stupcu
Ako želite odabrati posljednju ćeliju u stupcu A, samo uklonite “.row” s kraja i napišite .select.

Sub getLastUsedRow () Ćelije (Rows.Count, 1) .End (xlUp). Odaberite ‘Ovaj redak odabire posljednju ćeliju u stupcu End Sub

Naredba “.Select” odabire aktivnu ćeliju.
Dohvati stupac adrese zadnje ćelije
Ako želite unijeti adresu zadnje ćelije u stupac A, samo uklonite ".row" s kraja i napišite .adresu.

Sub getLastUsedRow () add = Cells (Rows.Count, 1) .End (xlUp) .address ‘Ovaj redak odabire posljednju ćeliju u stupcu Debug.print add End Sub

Raspon.Adresa funkcija vraća adresu aktivne ćelije.
Pronađi posljednju kolonu koja nije prazna u redu
To je gotovo isto kao pronalaženje posljednje ćelije koja nije prazna u stupcu. Ovdje dobivamo broj stupca posljednje ćelije s podacima u retku 4.

Sub getLastUsedCol () Dim last_col As Integer last_col = Cells (4, Columns.Count) .End (xlToLeft). Column 'Ovaj redak dobiva zadnji stupac Debug.Print last_col End Sub


Na slici možete vidjeti da vraća broj stupca zadnje neprazne ćelije u retku 4. Što je 4.
Kako radi?

Pa, mehanika je ista kao i pronalaženje posljednje ćelije s podacima u stupcu. Upravo smo koristili ključne riječi povezane sa stupcima.
Odaberite Skup podataka u Excelu pomoću VBA
Sada znamo kako doći do zadnjeg reda i zadnjeg stupca u Excelu pomoću VBA. Pomoću toga možemo lako odabrati tablicu ili skup podataka. Nakon odabira skupa podataka ili tablice, možemo izvršiti nekoliko operacija nad njima, poput copy-paste, oblikovanja, brisanja itd.
Ovdje imamo skup podataka. Ti se podaci mogu proširiti prema dolje. Samo je početna ćelija fiksirana, a to je B4. Zadnji redak i stupac nisu fiksni. Moramo dinamički odabrati cijelu tablicu pomoću vba.

VBA kôd za odabir tablice s praznim ćelijama

Sub select_table () Dim last_row, last_col As Long 'Dohvati zadnji red last_row = Ćelije (Redovi.Broj, 2). Kraj (xlUp) .Row' Dohvati zadnji stupac last_col = Ćelije (4, Stupci.Broj). Kraj (xlToLeft) . Stupac 'Odaberite cijeli raspon tablice (ćelije (4, 2), ćelije (last_row, last_col)). Odaberite End Sub

Kad ovo pokrenete, cijela tablica bit će odabrana u djeliću sekunde. Možete dodati nove retke i stupce. Uvijek će odabrati cijele podatke.

Prednosti ove metode:

  1. To je lako. Doslovno smo napisali samo jedan redak kako bismo dobili zadnji red s podacima. To olakšava.
  2. Brzo. Manje reda koda, manje vremena.
  3. Lako za razumjeti.
  4. Radi savršeno ako imate nespretnu tablicu podataka s fiksnom početnom točkom.

Nedostaci Range.End metode:

  1. Polazna točka mora biti poznata.
  2. Možete dobiti samo zadnju ćeliju koja nije prazna u poznatom retku ili stupcu. Kad vaša početna točka nije fiksna, bit će beskorisna. Što je vrlo manje vjerojatno da će se dogoditi.

2. Pronađi zadnji redak pomoću funkcije Find ()
Pogledajmo prvo kôd.

 Sub last_row () lastRow = ActiveSheet.Cells.Find ("*", redoslijed pretraživanja: = xlByRows, smjer pretraživanja: = xlPrethodni). Otklanjanje pogrešaka u ispisu LastRow End Sub


Kao što vidite na slici, ovaj kôd točno vraća zadnji red.
Kako radi?
Ovdje koristimo funkciju find za pronalaženje bilo koje ćelije koja sadrži bilo što koristeći operator zamjenske kartice "*". Zvjezdica se koristi za pronalaženje bilo čega, teksta ili broja.

Postavljamo redoslijed pretraživanja po redovima (redoslijed pretraživanja: = xlByRows). Excelu vba također kažemo smjer pretraživanja kao xlPrevious (searchdirection: = xlPrevious). Omogućuje funkciju traženja za pretraživanje s kraja lista, red po redu. Kad pronađe ćeliju koja sadrži bilo što, zaustavlja se. Koristimo metodu Range.Row za dohvaćanje posljednjeg retka iz aktivne ćelije.

Prednosti funkcije Find za dobivanje posljednje ćelije s podacima:

  1. Ne morate znati polazište. Dobivate zadnji red.
  2. Može biti generički i može se koristiti za pronalaženje posljednje ćelije s podacima na bilo kojem listu bez ikakvih promjena.
  3. Može se koristiti za pronalaženje posljednje instance određenog teksta ili broja na listu.

Nedostaci funkcije Find ():

  1. To je ružno. Previše argumenata.
  2. To je sporo.
  3. Ne može se koristiti za dobivanje posljednjeg stupca koji nije prazan. Tehnički, možete. Ali to postaje presporo.

3. Korištenje funkcije SpecialCells za dobivanje zadnjeg reda
Funkcija SpecialCells s argumentom xlCellTypeLastCell vraća zadnju korištenu ćeliju. Pogledajmo prvo kôd

Sub spl_last_row_ () lastRow = ActiveSheet.Cells.Posebne ćelije (xlCellTypeLastCell).Red Debug.Odštampaj lastRow End Sub


Ako pokrenete gornji kôd, dobit ćete broj retka zadnje korištene ćelije.

Kako radi?

Ovo je vba ekvivalent prečaca CTRL+End u Excelu. Odabire posljednju korištenu ćeliju. Ako snimite makronaredbu pritiskom na CTRL+End, dobit ćete ovaj kôd.

Sub Macro1 () '' Macro1 Macro '' ActiveCell.SpecialCells (xlLastCell). Odaberite End Sub

Upravo smo ga koristili za dobivanje broja retka zadnje korištene ćelije.

Bilješka: Kao što sam gore rekao, ova metoda će vratiti zadnju korištenu ćeliju, a ne zadnju ćeliju s podacima. Ako izbrišete podatke u zadnjoj ćeliji, iznad vba koda i dalje će se vraćati ista referenca ćelija jer je to bila "posljednja korištena ćelija". Morate prvo spremiti dokument da biste dobili zadnju ćeliju s podacima pomoću ove metode.

Brisanje listova bez upita za potvrdu pomoću VBA u programu Microsoft Excel

Dodajte i spremite novu radnu knjigu pomoću VBA -a u programu Microsoft Excel 2016

Prikažite poruku na statusnoj traci programa Excel VBA

Isključite poruke upozorenja pomoću VBA u programu Microsoft Excel 2016

Popularni članci:

Funkcija VLOOKUP u Excelu

COUNTIF u Excelu 2016

Kako koristiti funkciju SUMIF u Excelu