Hướng dẫn cách tìm dòng cuối trong VBA cho dữ liệu bảng Excel

Tại trang tính, chúng ta có thể sử dụng công thức hàm MATCH đơn giản để tìm dòng cuối cùng trong Excel. Tuy nhiên, công thức này không thể áp dụng cho dữ liệu bảng Excel. Thay vào đó, chúng ta cần đến sự hỗ trợ của phương pháp tìm dòng cuối trong VBA. Hãy cùng Gitiho tìm hiểu cách làm này trong bài viết ngày hôm nay nhé.

Xem thêm: Hướng dẫn sử dụng hàm MATCH tìm dòng cuối trong Excel

Tạo bảng Excel cần tìm dòng cuối trong VBA

Giả sử chúng ta có một bảng Excel như trong hình dưới đây. Nhiệm vụ lúc này là tìm dòng cuối cùng chứa dữ liệu trong bảng. Làm thế nào để thực hiện điều này? Chúng mình sẽ chỉ cho các bạn một số phương pháp tìm dòng cuối trong VBA hữu hiệu ngay sau đây.

Bước đầu tiên, chúng ta sẽ chuyển dữ liệu trong trang tính về dạng bảng Excel bằng cách chọn toàn bộ vùng dữ liệu > vào tab Insert > Table. Một hộp thoại hiện lên xác nhận lại phạm vi dữ liệu muốn tạo thành bảng. Chúng ta nhấn OK để hoàn thành thao tác.

Chúng ta đã có một bảng Excel hoàn chỉnh. Bây giờ, hãy cùng khám phá các ứng dụng tìm dòng cuối trong VBA cho bảng dữ liệu này nhé.

Cách tìm dòng cuối trong VBA cho dữ liệu bảng Excel

Tìm dòng cuối trong VBA bằng lệnh End(xlUp)

Cách thức hoạt động

Phương pháp đầu tiên chúng ta tìm hiểu trong bài viết là lệnh End(xlUp) của VBA Excel. Khi kết hợp lệnh End(xlUp) với một tọa độ ô, Excel sẽ hiểu rằng người dùng muốn di chuyển từ tọa độ ô đã chọn lên ô trên cùng chứa dữ liệu trong phạm vi trang tính. Nói một cách đơn giản, cách thức hoạt động của lệnh VBA Excel này về cơ bản giống như khi ta chọn một tọa độ ô, nhấn giữ phím Ctrl và phím mũi tên đi lên trên bàn phím.

Xem thêm: Hướng dẫn về các phím tắt thông dụng bạn nên biết trong Excel

Cách viết lệnh
 

Để tìm dòng cuối trong VBA bằng lệnh End(xlUp), chúng ta có công thức thường gặp nhất được viết như sau:

Tên Sheet.Cells(Rows.Count, Số cột).End(xlUp).Row

Trong đó:

Tên Sheet: Tên của Sheet cần thực hiện thao tác tìm dòng cuối cùng trong Excel.

Cells(Rows.Count, Số cột): Vị trí ô tính

• Rows.Count: Lệnh thực hiện đếm số hàng trong phạm vi đã chọn.
• Số cột: Vị trí cột muốn đếm.
• End(xlUp).Row: Lệnh tính toàn bộ số dòng của bảng tính ngược lên phía trên đến dòng xa nhất chứa dữ liệu.

Ví dụ

Áp dụng công thức trên vào ví dụ đề bài, chúng ta hãy tạo một Module trên cửa sổ VBA Excel và viết câu lệnh tìm dòng cuối trong VBA với dữ liệu bảng như sau:

Sub TimDongCuoi_Table_01()
Sheet1.Cells(2, 4).Value = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
End Sub

Lệnh này có ý nghĩa là: Giá trị của ô nằm ở vị trí giao điểm hàng 2 và cột 4 (ô D2) sẽ bằng toàn bộ số dòng của bảng tính ngược lên phía trên đến dòng xa nhất chứa dữ liệu. Như vậy, chúng ta chạy Macro và nhận được kết quả như sau:

Lệnh VBA Excel đã trả về số 20 trong ô D2, nghĩa là hàng thứ 20 là hàng cuối cùng chứa dữ liệu trong bảng Excel. Vậy là chúng ta đã hoàn thành áp dụng cách tìm dòng cuối trong VBA bằng lệnh End(xlUp) rồi.

Lưu ý

Khi sử dụng phương pháp này, bạn hãy lưu ý rằng dòng cuối được ghi nhận trong lệnh End(xlUp) là dòng cuối cùng của cột bạn đang áp dụng, hoàn toàn không phụ thuộc phía trong phạm vi có dữ liệu hay không. Điều này có nghĩa rằng khi bạn xóa bớt một số dòng dữ liệu trong bảng Excel thì kết quả trả về vẫn là 20. Còn trong trường hợp bạn thêm dữ liệu ngoài bảng tại các ô phía dưới của cột, Excel sẽ tự động tính thêm các dòng chứa dữ liệu đó.

Có thể nói, lệnh End(xlUp) là một trong những lệnh phổ biến nhất khi sử dụng VBA Excel. Bên cạnh ứng dụng tìm dòng cuối cùng trong Excel, một ứng dụng khác của lệnh này là tạo tham chiếu trang tính. Các bạn hãy tham khảo bài viết dưới đây để tìm hiểu thêm nhé.

Xem thêm: Hướng dẫn cách tạo tham chiếu trong Excel bằng lệnh VBA

Tìm dòng cuối trong VBA bằng lệnh End(xlUp)/End(x1Down) riêng với bảng Excel

Với cách này, chúng ta cần đặt tên cho bảng Excel bằng cách chọn bảng > vào tab Design > nhóm Properties > điền tên vào ô Table Name. Trong bài, chúng mình sẽ đặt tên bảng Excel là Table1.

Sau đó, chúng ta sẽ tiến hành viết lệnh VBA Excel dựa vào tên bảng đã đặt để giới hạn phạm vi dữ liệu tìm dòng cuối trong VBA.

Lệnh End(xIUp)

Cách viết lệnh

Khi áp dụng lệnh End(xIUp) cho riêng phạm vi bảng Excel, chúng ta sử dụng cách viết như sau:

Tên Sheet.Range("Tên bảng").End(xlUp).Row

Trong đó:

• Tên Sheet: Tên của Sheet chứa phạm vi cần thực hiện thao tác tìm dòng cuối cùng trong Excel.
• Range("Tên bảng"): Phạm vi thực hiện lệnh.
• End(xlUp).Row: Lệnh tính từ vị trí dòng đầu tiên của bảng ngược lên phía trên đến dòng xa nhất chứa dữ liệu.

Ví dụ:

Chúng ta thử áp dụng mẫu lệnh trên vào ví dụ đề bài để xem kết quả thu được từ cách tìm dòng cuối trong VBA này ra sao nhé. Câu lệnh cụ thể với trường hợp này là:

Sub TimDongCuoi_Table_02()
Sheet1.Cells(3, 4).Value = Sheet1.Range("Table1").End(xlUp).Row
End Sub

Các dòng lệnh VBA Excel này được hiểu là: Giá trị của ô nằm ở vị trí giao điểm hàng 3 và cột 4 (ô D3) sẽ bằng số dòng tính từ dòng đầu tiên ngược lên phía trên đến dòng xa nhất chứa dữ liệu tại bảng Table1. Kết quả Excel trả về như sau:

 

Con số hiển thị tại ô D3 là 1 thay vì kết quả đúng là 20. Lý do là vì tính từ dòng đầu tiên của bảng lên giới hạn phía trên chỉ có 1 hàng duy nhất. Cùng thử với lệnh End.(xlDown) để xem có sự khác biệt gì không nhé.

Lệnh End.(xlDown)

Cách thức hoạt động

Tương tự như lệnh End.(xlUp), lệnh End.(xlDown) về cơ bản có chức năng giống như thao tác chọn một ô bất kì trong trang tính, nhấn giữ phím Ctrl và phím mũi tên đi xuống. Lúc này, Excel sẽ di chuyển con trỏ chuột của bạn xuống vị trí dòng cuối cùng trong Excel chứa dữ liệu ở giới hạn dưới của trang

Cách viết lệnh

Chúng ta có lệnh VBA Excel như sau:

Tên Sheet.Range("Tên bảng").End(xlDown).Row

Trong đó:

• Tên Sheet: Tên của Sheet chứa phạm vi cần thực hiện thao tác tìm dòng cuối cùng trong Excel.
• Range("Tên bảng"): Phạm vi thực hiện lệnh.
• End(xlDown).Row: Lệnh tính từ vị trí dòng đầu tiên của về phía dưới đến dòng xa nhất chứa dữ liệu.

Ví dụ

Với ví dụ của đề bài, hãy sử dụng lệnh VBA Excel dưới đây để tìm dòng cuối trong VBA.

Sub TimDongCuoi_Table_03()
Sheet1.Cells(4, 4).Value = Sheet1.Range("Table1").End(xlDown).Row
End Sub

Câu lệnh này sẽ tiến hành trả giá trị số dòng tính từ dòng đầu tiên đến dòng cuối cùng trong bảng Table1 có chứa dữ liệu vào ô D4 của trang tính. Chúng ta cùng thử xem kết quả như thế nào nhé.

Như vậy, bạn có thể dễ dàng nhận thấy lệnh End.(xlDown) là sự lựa chọn phù hợp hơn so với lệnh End.(xlUp) khi cần áp dụng cách tìm dòng cuối trong VBA với dữ liệu bảng Excel.

Lưu ý

Khi sử dụng lệnh End.(xlDown), hãy ghi nhớ rằng nếu bảng của bạn chứa các dòng dữ liệu bị ẩn (Hide Row) thì câu lệnh VBA Excel sẽ không tính các dòng đó. Ngoài ra, nếu trong bảng có các dòng không chứa dữ liệu nằm xen giữa các nội dung thì lệnh sẽ chỉ tìm đến vị trí dòng trống xen giữa đầu tiên.

Tìm dòng cuối trong VBA bằng lệnh Range.Find

Cách thức hoạt động

Để khắc phục nhược điểm của các phương pháp kể trên, các bạn hãy sử dụng lệnh Range.Find. Lệnh VBA Excel này hoạt động tương tự như các hàm VLOOKUP, hàm INDEX hay hàm MATCH, tìm kiếm các giá trị cụ thể trong trang tính. Chúng ta có thể sử dụng lệnh Range.Find với tất cả các bảng, kể cả bảng có dòng trống xen giữa hay bảng có dòng cuối chứa công thức nhưng là kết quả rỗng.

Cách viết lệnh

Trong bài viết này, chúng ta sẽ cần đến cấu trúc lệnh như sau:

Tên Sheet.ListObjects("Tên bảng").Range.Find(What:="*", LookIn:=xlValues, SearchOrder=xlByRows, SearchDimension:=xlPrevious).Row

Trong đó:

• Tên Sheet: Tên của Sheet chứa bảng.
• ListObjects("Tên bảng"): Phạm vi thực hiện lệnh.
• Lệnh Range.Find: Lệnh tìm kiếm các đối tượng trong bảng khác giá trị rỗng.

Ví dụ

Hãy cùng áp dụng mẫu lệnh trên vào ví dụ bài viết để tìm dòng cuối trong VBA với dữ liệu bảng:

Sub TimDongCuoi_Table_04()
Sheet1.Cells(5, 4).Value = Sheet1.ListObjects("Table1").Range.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End Sub

Với lệnh này, Excel sẽ trả về ô D5 giá trị bằng với tổng số dòng chứa giá trị khác rỗng trong bảng Table1. Nếu ta thay đổi đối tượng LookIn trong công thức Range.Find từ xlValues thành xlFormulas, ta sẽ tìm được tất cả các hàng trong bảng chứa công thức, ngay cả khi kết quả của công thức là giá trị rỗng.

Nhược điểm duy nhất của phương pháp Range.Find chính là độ phức tạp của công thức. Tuy nhiên, độ hiệu quả của lệnh VBA Excel sẽ đảm bảo dữ liệu của bạn được trả về chính xác như yêu cầu.

Giả sử trường hợp bạn cần dùng đến công thức tìm dòng cuối cùng trong Excel nhiều lần, hãy tham khảo cách tạo một hàm riêng của mình bằng công cụ VBA Excel nhé.

Xem thêm: Hướng dẫn cách tự tạo hàm UDF trong Excel bằng lệnh VBA

Tổng kết

Qua bài viết này, chúng ta đã tìm hiểu cách tìm dòng cuối trong VBA áp dụng cho dữ liệu dạng bảng Excel. Ngoài ra, còn rất nhiều ứng dụng hữu ích của VBA Excel đang chờ bạn khám phá trên blog Gitiho. Hãy tham khảo các bài viết về chủ đề này và đăng kí học  VBA với chúng mình để thành thạo công cụ xử lý dữ liệu VBA nhanh chóng nhất nhé.

Gitiho chúc các bạn thành công!