3 cách tốt nhất để tìm hàng và cột không trống cuối cùng bằng VBA
Tìm hàng và cột được sử dụng cuối cùng là một trong những nhiệm vụ cơ bản và quan trọng đối với bất kỳ quá trình tự động hóa nào trong excel bằng VBA. Để biên dịch trang tính, sổ làm việc và sắp xếp dữ liệu tự động, bạn bắt buộc phải tìm giới hạn của dữ liệu trên trang tính.
Bài viết này sẽ giải thích mọi phương pháp tìm hàng và cột cuối cùng trong excel theo những cách dễ dàng nhất.
1. Tìm hàng không trống cuối cùng trong cột bằng cách sử dụng Range.End
Hãy xem mã trước. Tôi sẽ giải thích nó thư.
Sub getLastUsedRow() Dim last_row As Integer last_row = Cells(Rows.Count, 1).End(xlUp).Row ‘This line gets the last row Debug.Print last_row End Sub
Sub ở trên tìm hàng cuối cùng trong cột 1.
Nó hoạt động như thế nào?
Nó giống như đi đến hàng cuối cùng trong trang tính và sau đó nhấn phím tắt CTRL + UP.
Cells (Rows.Count, 1): Phần này chọn ô trong cột A. Rows.Count cho 1048576, thường là hàng cuối cùng trong trang tính excel.
Cells(1048576, 1)
Cùng với lệnh này chọn hàng cuối cùng có dữ liệu.
Cells(Rows.Count, 1).End(xlUp)
Xem cách dễ dàng tìm thấy các hàng cuối cùng với dữ liệu. Phương pháp này sẽ chọn hàng cuối cùng trong dữ liệu bất kể các ô trống trước nó. Bạn có thể thấy rằng trong hình ảnh chỉ có ô A8 có dữ liệu. Tất cả các ô trước đó đều để trống ngoại trừ ô A4.
Chọn ô cuối cùng có dữ liệu trong cột
Nếu bạn muốn chọn ô cuối cùng trong cột A thì chỉ cần xóa “.row” ở cuối và viết .select.
Sub getLastUsedRow() Cells(Rows.Count, 1).End(xlUp).Select ‘This line selects the last cell in a column End Sub
Lệnh “.Select” chọn ô hiện hoạt.
Nhận cột địa chỉ của ô cuối cùng
Nếu bạn muốn lấy địa chỉ của ô cuối cùng trong cột A thì chỉ cần xóa “.row” ở cuối và viết .address.
Sub getLastUsedRow() add=Cells(Rows.Count, 1).End(xlUp).address ‘This line selects the last cell in a column Debug.print add End Sub
Hàm Range.Address * trả về địa chỉ của ô hoạt động.
Tìm cột không trống cuối cùng trong hàng
Nó gần giống như việc tìm ô không trống cuối cùng trong một cột. Ở đây chúng ta đang nhận được số cột của ô cuối cùng với dữ liệu ở hàng 4.
Sub getLastUsedCol() Dim last_col As Integer last_col = Cells(4,Columns.Count).End(xlToLeft).Column ‘This line gets the last column Debug.Print last_col End Sub
Bạn có thể thấy trong hình ảnh rằng nó đang trả về số cột của ô không trống cuối cùng trong hàng 4. Đó là 4.
Nó hoạt động như thế nào?
Cơ học cũng giống như tìm ô cuối cùng với dữ liệu trong một cột.
Chúng tôi chỉ sử dụng các từ khóa liên quan đến cột.
Chọn Tập dữ liệu trong Excel bằng VBA
Bây giờ chúng ta đã biết, cách lấy hàng cuối cùng và cột cuối cùng của excel bằng VBA.
Sử dụng nó, chúng tôi có thể chọn một bảng hoặc tập dữ liệu dễ dàng. Sau khi chọn tập dữ liệu hoặc bảng, chúng ta có thể thực hiện một số thao tác trên chúng, như sao chép-dán, định dạng, xóa, v.v.
Ở đây chúng tôi có tập dữ liệu. Dữ liệu này có thể mở rộng xuống dưới. Chỉ ô bắt đầu là cố định, là B4. Hàng và cột cuối cùng không cố định. Chúng ta cần chọn động toàn bộ bảng bằng vba.
Mã VBA để chọn bảng có ô trống
Sub select_table() Dim last_row, last_col As Long 'Get last row last_row = Cells(Rows.Count, 2).End(xlUp).Row 'Get last column last_col = Cells(4, Columns.Count).End(xlToLeft).Column 'Select entire table Range(Cells(4, 2), Cells(last_row, last_col)).Select End Sub
Khi bạn chạy điều này, toàn bộ bảng sẽ được chọn trong một phần của giây. Bạn có thể thêm hàng và cột mới. Nó sẽ luôn chọn toàn bộ dữ liệu.
Lợi ích của phương pháp này:
-
Dễ thôi. Theo nghĩa đen, chúng tôi chỉ viết một dòng để lấy dữ liệu hàng cuối cùng.
Điều này làm cho nó dễ dàng.
-
Nhanh. Ít dòng mã hơn, mất ít thời gian hơn.
-
Dễ hiểu.
-
Hoạt động hoàn hảo nếu bạn có bảng dữ liệu vụng về với điểm xuất phát cố định.
Nhược điểm của Range.End method:
-
Điểm bắt đầu phải biết.
-
Bạn chỉ có thể lấy ô không trống cuối cùng trong một hàng hoặc cột đã biết. Khi điểm xuất phát của bạn không cố định, nó sẽ trở nên vô ích. Điều này rất ít có khả năng xảy ra.
2. Tìm hàng cuối cùng bằng hàm Find ()
Hãy xem mã trước.
Sub last_row()
lastRow = ActiveSheet.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
Debug.Print lastRow
End Sub
Như bạn có thể thấy trong hình ảnh, mã này trả về chính xác hàng cuối cùng.
Nó hoạt động như thế nào?
Ở đây chúng tôi sử dụng hàm find để tìm bất kỳ ô nào chứa bất kỳ thứ nào bằng cách sử dụng toán tử ký tự đại diện “*”. Dấu hoa thị được sử dụng để tìm bất kỳ thứ gì, văn bản hoặc số.
We set search order by rows (searchorder:=xlByRows). We also tell excel vba the direction of search as xlPrevious (searchdirection:=xlPrevious). It makes find function to search from end of the sheet, row wise. Once it find a cell that contains anything, it stops. We use the Range.Row method to fetch last row from active cell.
Lợi ích của chức năng Tìm để lấy dữ liệu ô cuối cùng:
-
Bạn không cần biết điểm bắt đầu. Nó chỉ giúp bạn có hàng cuối cùng. . Nó có thể là chung và có thể được sử dụng để tìm ô cuối cùng với dữ liệu trong bất kỳ trang tính nào mà không có bất kỳ thay đổi nào.
-
Có thể được sử dụng để tìm bất kỳ trường hợp cuối cùng nào của văn bản hoặc số cụ thể trên trang tính.
Nhược điểm của hàm Find ():
-
Nó là xấu xí. Quá nhiều đối số.
-
Nó chậm.
-
Không thể sử dụng để lấy cột không trống cuối cùng. Về mặt kỹ thuật, bạn có thể. Nhưng nó trở nên quá chậm.
3. Sử dụng hàm SpecialCells để lấy hàng cuối cùng
Hàm SpecialCells với đối số xlCellTypeLastCell trả về ô được sử dụng cuối cùng. Hãy xem mã trước
Sub spl_last_row_() lastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row Debug.Print lastRow End Sub
Nếu bạn chạy mã trên, bạn sẽ nhận được số hàng của ô được sử dụng cuối cùng.
Làm thế nào nó hoạt động?
Đây là vba tương đương với phím tắt CTRL + End trong excel. Nó chọn ô được sử dụng cuối cùng. Nếu ghi macro trong khi nhấn CTRL + End, bạn sẽ nhận được mã này.
Sub Macro1() ' ' Macro1 Macro ' ' ActiveCell.SpecialCells(xlLastCell).Select End Sub
Chúng tôi vừa sử dụng nó để lấy số hàng của ô được sử dụng gần đây nhất.
Lưu ý: * Như tôi đã nói ở trên, phương thức này sẽ trả về ô được sử dụng cuối cùng không phải ô cuối cùng với dữ liệu. Nếu bạn xóa dữ liệu trong ô cuối cùng, mã vba trên sẽ vẫn trả về cùng một tham chiếu ô, vì đó là “ô được sử dụng cuối cùng”. Bạn cần lưu tài liệu trước để lấy ô cuối cùng với dữ liệu bằng phương pháp này.