Nguyễn Tiến Nguyễn Tiến -- Published Jan 14, 2022
Hàm dưới đây sẽ trả về dòng cuối cùng có dữ liệu của sheet hiện hành. Cách này xác định dựa vào thuộc tính .UsedRange.Rows.Count và .UsedRange.Row của 1 sheet. Lưu ý định nghĩa có chứa dữ liệu theo cách này nghĩa là không chỉ cell không rỗng mà còn cả cell có format, fill, border, thậm chí 1 cell với fill= nofill cũng được hiểu là có chứa dữ liệu. . Public Function LastUsedRow() as Long
LastUsedRow = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1
End Function
Tương tự, hàm dưới đây sẽ trả về dòng cuối có chứa thông tin của 1 sheet có tên là sheetName: Public Function LastUsedRow(sheetName As String) As Long
Dim ws As Worksheet
Set ws = Sheets(sheetName)
LastUsedRow = ws.UsedRange.Rows.Count + ws.UsedRange.Row - 1
End Function
Cách khác
dựa vào thủ tục .xlEnd.Up (tương tự như ta bấm phím Ctrl+ mũi tên lên trong Excel. Bản chất của cách này là chọn dòng cuối cùng của 1 cột trong sheets sau đó bấm Ctrl+ mũi tên lên thì con trỏ sẽ nhảy đến dòng cuối cùng có chứa dữ liệu trong cột đó. "Có chứa dữ liệu" ở cách này được hiểu khác với cách dùng .UsedRange.Rows.Count, chỉ những ô có không rỗng mới được coi là có chứa dữ liệu, cells rỗng nhưng có format thì cũng coi như không có dữ liệu. Theo cách này thì hàm tìm dòng cuối không
rỗng của cột columnName trong sheets sheetName như sau: Public Function LastUsedRow(sheetName As String, columnName As Long) As Lon
Dim ws As Worksheet
Set ws = Sheets(sheetName)
LastUsedRow = ws.Cells(ws.Rows.Count, columnName).End(xlUp).Row
End Function
Xin chào tất cả các thành viên của tuhocvba.net thân mến!
Hôm nay tôi tiếp tục chia sẻ tới các bạn 1 kết quả trong công việc mà tôi đã hoàn thành dựa vào việc áp dụng thành công lý thuyết học được từ tuhocvba.net
Tôi tạo bài viết này với phần nhiều mong muốn là tạo được nguồn cảm hứng cho các bạn có nhu cầu tự học vba, cũng như là 1 tham khảo thêm cho
phương pháp tư duy thực hành sử dụng code vba trong công việc.
Trước khi vào bài viết tôi xin chân thành cảm ơn các bạn tuhocvba;vbano1;Euler;bvtvba; giaiphapvba, bởi các bài viết chất lượng của các bạn đã cung cấp cho tôi những lý thuyết cơ bản gồm:
phần lý thuyến vòng lặp (for….to….) trên diễn đàn ;tôi tham khảo ở đây: Bạn cần đăng nhập để thấy link
phần lý thuyến code tìm dòng cuối trên diễn đàn ;tôi tham khảo
ở đây: Bạn cần đăng nhập để thấy link phần lý thuyến gán biến trên diễn đàn ;tôi tham khảo ở đây: Bạn cần đăng nhập để thấy link phần lý thuyết tìm cột theo số trên diễn đàn ;tôi tham khảo ở đây: Bạn cần đăng nhập để thấy link các video VBA02 Logic For Next; VBA08 find last row trên kênh youtube của tuhocvba Bạn cần đăng nhập để thấy link Nào Chúng ta cùng bắt đầu!!!
A/Bối cảnh ,
đặc điểm công việc thực tế của tôi:
Mỗi ngày , tôi có 1 bản dữ liệu (được kết xuất từ phần mềm của Công ty), sau khi được đồng nghiệp biến đổi, dữ liệu này được trình bày theo kiểu dạng dàn ngang trên 1 sheet, có 2 cặp tiêu đề lặp đi lặp lại ở tất cả các cột ( tiêu đề: day/month và tiêu đề: Profit), số lượng dòng ở mỗi cặp (2 cột) là không giống nhau lúc tăng lúc giảm, chi tiết như file ảnh dưới đây: Bạn cần đăng nhập để thấy đa phương tiện Và với kiểu trình bày dữ liệu
nhận được từ đồng nghiệp này, tôi cần tạo ra kết quả Total ở sau dòng cuối của các cột Profit,và tạo kết quả đếm số lượng ngày ở sau dòng cuối của các cột Day/Month; tạo Format các kết quả dạng bôi đậm (bold = True). Dưới đây là file ảnh khi công việc hoàn thành: Bạn cần đăng nhập để thấy đa phương tiện B/Các hướng tư duy tổng quát
1/ Tư duy theo hướng người dùng sử dụng công thức (Sum, Count) trực tiếp trên bảng tính, kết hợp
thao tác thủ công bàn phím và trỏ chuột. Nếu theo hướng này, các bạn chắc hẳn cũng đã đoán ra các bước thực hiện rồi phải không nào? Quá đơn giản các bạn nhỉ? (…đưa trỏ chuột đên ô cuối của từng cột, nhập công thức Sum(…), count(…), Ctrl+B, cột tiếp theo và thao tác cho đến cột cuối cùng…) 2/Tư duy theo hướng áp dụng VBA để tạo kết quả trên bảng tính. Nếu theo hướng này, các bạn sẽ nảy sinh những suy nghĩ khái quát gì?
(về phía tôi, khi nhìn bảng tính này)
Điều
thứ 1: tôi nhận thấy việc xuất hiện của các kết quả trong vùng sẽ có tính chất lặp, nếu làm thủ công tính chất lặp ở đây chính là các thao tác chuyển tới các cột kế tiếp để tính toán ; dựa vào tính chất lặp tôi nghĩ tới việc đưa vào code của mình vòng lặp (for….to….), phần lý thuyết vòng lặp (for….to….) trên diễn đàn các bạn có thể tham khảo ở đây Bạn cần đăng nhập để thấy link Điều thứ 2: tôi nghĩ đến điểm đầu và điểm cuối trên bảng tính (1 hình ảnh tư duy
của tôi như sau). Bạn cần đăng nhập để thấy đa phương tiện Như trong hình ảnh trên, các bạn có nhận thấy rằng:
+Điêm đầu luôn bắt đầu từ số 1,
+ Điểm cuối là các dòng cuối (vậy là rõ ràng chúng ta cần áp dụng code tìm dòng cuối), phần lý thuyết code tìm dòng cuối trên diễn đàn các bạn có thể tham khảo ở đây. Bạn cần đăng nhập để thấy link hoặc xem video VBA08 find last row trên kênh youtube của tuhocvba Bạn cần đăng
nhập để thấy link + Còn 1 điểm cuối quan trong hơn cả là cột cuối. (các bạn có thể vào google gõ tìm cột cuối), tương tự như lastrow nó có dạng phổ cập như sau: LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
Tổng quát lại: chúng ta sử dụng VBA để có lần lượt các thông số của các điểm đầu cuối, và truy cập vào các vùng trên bảng tính dựa vào các thông số đó để tạo ra kết quả mong muốn C/ Các bước thực hiện áp dụng VBA để tạo kết
quả trên bảng tính
Bước 1 : Tìm các điểm cuối
Trước khi bắt đầu làm việc với VBA, Một câu hỏi thú vị cần trả lời là chúng ta cần áp dụng code để tìm ra dòng cuối trước hay tìm cột cuối trước? .
Các bạn có nhận thấy rằng nếu tìm dòng cuối trước thì rõ ràng là chúng ta sẽ cần có nhiều kết quả của các dòng cuối ứng với mỗi cột trong bảng tính ( ví dụ dòng cuối cột A, cột B, cột C….). Tuy nhiên để làm được việc này VBA cần phải có 1 điểm dừng. Nếu không có điểm dừng VBA sẽ tìm
đến tận cột XFD chăng??? (cột cuối cùng của Excel 2010 đấy các bạn à).
Vậy là tôi sẽ đi tìm cột cuối của bảng tính trước các bạn nhé!
1.1 Thủ tục gán biến Dim i as long, buf As String, lastrow As Long
Dim LastCol As Intege
phần lý thuyết gán biến trên diễn đàn các bạn có thể tham khảo ở đây Bạn cần đăng nhập để thấy link 1.2 Tìm cột cuối của bảng tính ' Find cot cuoi cua bang tinh
With ActiveSheet
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
Kết quả chạy code này là 12: Bạn cần đăng nhập
để thấy đa phương tiện 1.3 Tìm các dòng cuối của bảng tính
Trước khi vào CODE. Các bạn xem lại phần lý thuyết code tìm dòng cuối trên diễn đàn ở đây. Bạn cần đăng nhập để thấy link ; hoặc xem VBA08 find last row trên kênh youtube của tuhocvba Bạn cần đăng nhập để thấy link Và với lý thuyết chúng ta dễ dàng tìm được 1 dòng cuối ở 1 cột được chỉ định ;ví dụ ta tìm ở cột L: lastrow=.cells(.rows.count,"L").End(xlUp).Row
Kết quả chạy
code này là 20: Bạn cần đăng nhập để thấy đa phương tiện Tuy nhiên thực tế ở bảng này, chúng ta cần tới 12 kết quả của dòng cuối (từ dòng cuối cột A cho đến cột thứ 12 là cột L).
Có 1 cách dễ dàng là ta đặt ra 12 cái biến từ lr1,lr2,…lr12, và tạo ra 12 cái dòng code cho từng cột chỉ định từ cột A đến cột L. Nếu Làm cách này thì thà chúng ta làm thủ công còn nhanh hơn phải không các bạn?
Nhưng các bạn để ý; từ suy nghĩ giản đơn trên, chúng ta có được mấu chốt ở đây là thay
vì đặt ra 12 cái biến và chạy 12 dòng code. Chúng ta đặt 1 biến và chạy 1 dòng code, khác biệt là dòng code này chạy lặp 12 lần. Vậy là rõ ràng ; chúng ta cần kết hợp với vòng lặp (for….to….) để tìm ra các dòng cuối
Trước khi vào CODE. Các bạn xem lại 1 lần nữa phần lý thuyết vòng lặp (for….to….) ở đây. Bạn cần đăng nhập để thấy link và xem video VBA02 Logic For Next trên kênh youtube của tuhocvba Bạn cần đăng nhập để thấy link ở đây có 2 cách kết hợp
for...to...:
cách 1: For i = 1 to lastcol ‘ lastcol có kết quả = 12
lastrow = .Cells(Rows.Count, i).End(xlUp).Row ‘ Tìm dòng cuối chạy từ I đến lastcol
Do thời gian có hạn nên tôi không đi thêm bất cứ chi tiết nào cho cách 1, các bạn có thể tự nghiên cứu và phát triển thêm theo cách 1, rất mong nhận được chia sẻ từ các bạn
cách 2 (tôi ưu tiên thực hiện cách này vì tôi muốn áp dụng phần lý thuyết tìm cột theo số, mà tôi học được ở đây Bạn cần đăng nhập để thấy link ):
Tôi xin tóm lược như sau: Trước khi dùng
vòng lặp (for….to….); ta sử dụng 1 biến trung gian để chuyển đổi số của biến i sang Text. Ví dụ khi i = 1, ta chuyển đổi 1 thành
A, khi I = 2 thành B… khi i=12 thành L. Ta có Code của biến trung gian như sau buf = Cells(1, i).Address(False, False) 'Buf dung de Chuyen doi Cot tu dang so sang dang text
buf = Left(buf, Len(buf) - 1) 'Buf dung de Chuyen doi Cot tu dang so sang dang text
Biến trung gian hoạt động rất tuyệt vời; chúng ta lắp biến này vào vòng lặp, code như sau: ' Su dung vong lap for...to ket hop cac bien so LastCol, Lastrow
For i = 1 To LastCol
buf = Cells(1, i).Address(False, False) 'Buf dung de Chuyen doi Cot tu dang so sang dang text
buf = Left(buf, Len(buf) - 1) 'Buf dung de Chuyen doi Cot tu dang so sang dang text
' Find dong cuoi theo tung Lastcol, luc nay Lastcol da duoc chuyen doi sang text thong qua Buf
lastrow = .Cells(Rows.Count, buf).End(xlUp).Row
-------------------------------------
(Tôi Sẽ viết tiếp ở bài
dưới) | 
