Hướng Dẫn Cách Tìm Dòng Cuối Cùng Trong Excel, Định Vị Và Đặt Lại Ô Cuối Cùng Trên Trang Tính

Tạo bảng Excel buộc phải tìm mẫu cuối vào VBA

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

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

Chúng ta đã có một bảng Excel trả chỉnh. Bây giờ, hãy cùng tìm hiểu các áp dụng tìm cái cuối vào VBA mang đến bảng tài liệu này nhé.

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

Tìm cái cuối trong VBA bởi lệnh End(xl
Up)

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

Phương pháp đầu tiên bọn họ tìm phát âm trong bài viết là lệnh End(xl
Up) của VBA Excel. Khi phối hợp lệnh End(xl
Up) với một tọa độ ô, Excel đã hiểu rằng người tiêu dùng muốn dịch rời từ tọa độ ô đã chọn lên ô trên cùng chứa tài liệu trong phạm vi trang tính. Nói một cách 1-1 giản, cách thức hoạt động vui chơi của lệnh VBA Excel này về cơ phiên bản giống như lúc ta lựa chọn một tọa độ ô, nhận giữ phím Ctrl và phím mũi tên tăng trưởng trên bàn phím.

Cách viết lệnh

Để tìm dòng cuối vào VBA bằng lệnh End(xl
Up), bọn họ 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(xl
Up).Row

Trong đó:

Tên Sheet: tên của Sheet nên thực hiện làm việc tìm dòng ở đầu cuối trong Excel.

Cells(Rows.Count, Số cột): địa điểm ô tính

Ví dụ

Áp dụng cách làm trên vào ví dụ như đề bài, bọn họ hãy chế tác một Module trên cửa sổ VBA Excel cùng viết câu lệnhtìm loại cuối vào VBAvới dữ liệu bảng như sau:

Sub Tim
Dong
Cuoi_Table_01()Sheet1.Cells(2, 4).Value = Sheet1.Cells(Rows.Count, 1).End(xl
Up).Row
End Sub

Lệnh này có ý nghĩa sâu sắc là: quý giá của ô nằm ở chỗ giao điểm mặt hàng 2 cùng cột 4 (ô D2) vẫn bằngtoàn bộ số loại của bảng tính ngược lên phía trên đến loại xa nhất cất dữ liệu.Như vậy, họ chạy Macro và nhận được tác dụng như sau:

Lệnh VBA Excel đã trả về số đôi mươi trong ô D2, nghĩa là mặt hàng thứ trăng tròn là hàng ở đầu cuối chứa dữ liệu trong bảng Excel. Vậy là họ đã kết thúc áp dụng biện pháp tìm loại cuối vào VBAbằng lệnh End(xl
Up) rồi.

Lưu ý

Khi sử dụng phương thức này, các bạn hãy chú ý rằng loại cuối được ghi nhận trong lệnh End(xl
Up) là dòng sau cùng của cột nhiều người đang áp dụng, trọn vẹn không nhờ vào phía vào phạm vi có tài liệu hay không. Điều này còn có nghĩa rằng khi chúng ta xóa bớt một vài dòng dữ liệu trong bảng Excel thì kết quả trả về vẫn luôn là 20. Còn vào 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ẽ auto tính thêm các dòng chứa tài liệu đó.

Có thể nói, lệnh End(xl
Up) là trong những lệnh thịnh hành nhất khi thực hiện VBA Excel. Lân cận ứng dụng tìm kiếm dòng cuối cùng trong Excel, một ứng dụng khác của lệnh này là chế tạo ra tham chiếu trang tính. Chúng ta hãy tham khảo bài viết dưới trên đây để xem thêm nhé.

Tìm mẫu cuối trong VBA bởi lệnh End(xl
Up)/End(x1Down) riêng với bảng Excel

Với biện pháp này, họ cần để tên cho bảng Excel bằng cách chọn bảng > vào tab thiết kế > nhóm Properties > điền tên vào ô Table Name. Trong bài, bọn chúng mình sẽ đặt thương hiệu bảng Excel là Table1.

Sau đó, chúng ta sẽ thực hiện viết lệnh VBA Excel phụ thuộc tên bảng đang đặt để giới hạn phạm vi dữ liệu tìm loại cuối vào VBA.

Cách viết lệnh

Khi áp dụng lệnh End(x
IUp) mang đến riêng phạm vi bảng Excel, bọn họ sử dụng bí quyết viết như sau:

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

Trong đó:

Ví dụ:

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

Sub Tim
Dong
Cuoi_Table_02()Sheet1.Cells(3, 4).Value = Sheet1.Range("Table1").End(xl
Up).Row
End Sub

Các mẫu lệnh VBA Excel này được phát âm là: cực hiếm của ô nằm ở trong phần giao điểm mặt hàng 3 cùng cột 4 (ô D3) sẽ bằngsố mẫu tính từ dòng thứ nhất ngược lên bên trên đến cái xa độc 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 bởi vì kết quả và đúng là 20. Vì sao là vì tính trường đoản cú dòng thứ nhất của bảng lên giới hạn bên trên chỉ có 1 hàng duy nhất. Cùng thử cùng với lệnh End.(xl
Down) nhằm xem có sự khác hoàn toàn gì ko nhé.

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

Tương từ bỏ như lệnh End.(xl
Up), lệnh End.(xl
Down) về cơ phiên bản có tác dụng giống như thao tác lựa chọn 1 ô bất kỳ trong trang tính, nhận giữ phím Ctrl và phím mũi tên đi xuống. Thời gian này, Excel sẽ dịch rời con trỏ chuột của doanh nghiệp xuống địa chỉ dòng sau 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 gồm lệnh VBA Excel như sau:

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

Trong đó:

Ví dụ

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

Sub Tim
Dong
Cuoi_Table_03()Sheet1.Cells(4, 4).Value = Sheet1.Range("Table1").End(xl
Down).Row
End Sub

Câu lệnh này sẽ tiến hành trả quý giá số loại tính trường đoản cú dòng thứ nhất đến dòng cuối cùng trong bảng Table1 gồm chứa tài liệu vào ô D4 của trang tính. Chúng ta cùng demo xem tác dụng như gắng nào nhé.

Như vậy, bạn có thể dễ dàng nhận biết lệnh End.(xl
Down) là sự việc lựa chọn tương xứng hơn so với lệnh End.(xl
Up) lúc cần áp dụng cách tìm mẫu cuối vào VBA với dữ liệu bảng Excel.

Lưu ý

Khi sử dụng lệnh End.(xl
Down), hãy ghi nhớ rằng ví như bảng của người tiêu dùng chứa các dòng tài 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 như trong bảng có các dòng ko chứa tài liệu nằm xen giữa những nội dung thì lệnh đã chỉ tìm đến vị trí loại trống xen thân đầu tiên.

Tìm cái cuối vào VBA bằng lệnh Range.Find

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

Để tự khắc phục điểm yếu kém của các phương thức kể trên, chúng ta hãy sử dụng lệnh Range.Find. Lệnh VBA Excel này vận động tương từ bỏ như những hàm VLOOKUP, hàm INDEX giỏi hàm MATCH, search kiếm các giá trị ví dụ trong trang tính. Bạn cũng có thể sử dụng lệnh Range.Find với tất cả các bảng, của cả bảng có dòng trống xen giữa xuất xắc bảng bao gồm dòng cuối chứa phương pháp nhưng là công dụng rỗng.

Cách viết lệnh

Trong bài viết này, họ sẽ yêu cầu đến kết cấu lệnh như sau:

Tên
Sheet.List
Objects("Tên bảng").Range.Find(What:="*", Look
In:=xl
Values, Search
Order=xl
By
Rows, Search
Dimension:=xl
Previous).Row
Trong đó:

Ví dụ

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

Sub Tim
Dong
Cuoi_Table_04()Sheet1.Cells(5, 4).Value = Sheet1.List
Objects("Table1").Range.Find(What:="*", Look
In:=xl
Values, Search
Order:=xl
By
Rows, Search
Direction:=xl
Previous).Row
End Sub
Với lệnh này, Excel đang trả về ô D5 giá trị bởi với tổng số dòng chứa quý hiếm khác rỗng trong bảng Table1. Nếu như ta thay đổi đối tượng Look
In trong công thức Range.Find tự xl
Values thành xl
Formulas, ta sẽ tìm kiếm được tất cả các hàng trong bảng chứa công thức, ngay cả khi hiệu quả của cách làm là quý hiếm rỗng.

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

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

Tổng kết

Qua nội dung bài viết này, bọn họ đã tìm hiểu cách tìm dòng cuối vào VBA áp dụng cho tài liệu dạng bảng Excel. Ngoài ra, còn tương đối nhiều ứng dụng hữu ích của VBA Excel đang hóng bạn tìm hiểu trên emcanbaove.edu.vn Gitiho. Hãy xem thêm các nội dung bài viết về chủ đề này cùng đăng kí khóa đào tạo Tuyệt đỉnh VBA với bọn chúng mình nhằm thành thạo hiện tượng xử lý dữ liệu VBA mau lẹ nhất nhé.

Hàm Match là một hàm được ứng dụng không ít trong Excel nhằm tìm kiếm vị trí của một giá trị trong một vùng. Và chúng ta còn rất có thể tìm chiếc cuối bao gồm chứa dữ liệu của một cột bằng cách sử dụng hàm Match. Làm việc này như vậy nào? Hãy cùng Học Excel Online tìm hiểu cách thực hiện nhé.

Hãy cùng khám phá qua lấy một ví dụ sau:

Yêu ước của bọn họ là tìm chiếc cuối gồm chứa dữ liệu ở cột B, cột C cùng điền hiệu quả vào ô B15, C15. Trong các số ấy cột B tìm theo dòng cuối chứa tài liệu dạng Text, cột C tìm kiếm theo mẫu cuối chứa dữ liệu dạng Số.

Xem nhanh

Nguyên tắc xác minh dòng cuối có tài liệu của hàm Match

Do đó đối tượng người sử dụng lookup_value của hàm Match là loại tài liệu nào thì sẽ trả về hiệu quả theo loại dữ liệu đó. Trong Excel gồm 2 loại tài liệu cơ bản là loại Text và một số loại Số (Number)

Xác định vị trí ô cuối cùng chứa ký kết tự Text

Để xác định vị trí ô sau cùng chứa cam kết tự text trong một vùng bởi hàm Match thì chúng ta cần phải tạo ra 1 giá trị thỏa mãn điều kiện: Dạng text với là ký kết tự vô cùng lớn.

Để có tác dụng được điều này họ sẽ kết hợp với hàm REPT. Đây là hàm giúp tạo ra 1 chuỗi Text lớn phụ thuộc việc nhân bản chính cam kết tự lên những lần. Trong Excel 1 chuỗi chỉ lý lẽ tối nhiều là 255 cam kết tự. Vì đó chúng ta cũng có thể viết hàm như sau:

=MATCH(REPT(“z”,255),B2:B15)

Trong đó:

Kết trái thu được như sau:

Xác định vị trí ô ở đầu cuối chứa ký tự Số

Tương từ như bí quyết tìm ký tự Text, tuy nhiên để có thể tạo ra được một ký tự số thì bạn có thể thay ký tự z bằng 1 số ngẫu nhiên trong hàm REPT.

Tuy nhiên hàm REPT trả về công dụng là dạng Text. Cho nên vì vậy để chuyển số lượng của hàm REPT về dạng số thì ta đặt hàm REPT vào hàm VALUE.

Xem thêm: Tổng Hợp 7 Hàm Làm Tròn Hàng Nghìn Trong Excel Đơn Giản, Dễ Làm

Công thức tại ô C16 là:

=MATCH(VALUE(REPT(9,255)),C2:C15)

Kết trái thu được như sau:

Như vậy bằng cách sử dụng hàm Match kết hợp với một số hàm như REPT, VALUE bọn họ đã rất có thể tìm được loại cuối gồm chứa dữ liệu trong một cột. Hình như hoàn toàn rất có thể áp dụng nhằm tìm địa điểm ô sau cùng chứa dữ liệu trong 1 hàng bằng cách áp dụng tương tự cách làm này.

Tham khảo: Tìm loại cuối gồm chứa tài liệu bằng VBA Excel

Ngoài ra học Excel Online xin trình làng với các bạn Khóa học Excel tự cơ phiên bản tới nâng cấp dành mang đến người đi làm. Đây là 1 khóa học tập rất đầy đủ kiến thức và bổ trợ rất tuyệt vời cho chính mình trong việc làm quen với Excel, sử dụng các công cụ, những hàm trong Excel để giúp bạn thao tác làm việc trên Excel được xuất sắc hơn, hiệu quả hơn. Chi tiết xem tại: