Trong bài viết sử dụng Autofilter trong VBA (Phần 1) Taimienphi.vn đã giới thiệu cho bạn về Autofilter, công thức Autofilter như thế nào, ... . Trong bài viết Sử dụng Autofilter trong VBA (Phần 2) dưới đây Taimienphi.vn sẽ giới thiệu cho bạn một số ví dụ để hình dung cách sử dụng Autofilter trong VBA nhé
Bài viết dưới đây Taimienphi.vn sẽ giới thiệu cho bạn một số ví dụ về cách sử dụng Autofilter trong VBA. Ngoài ra bạn đọc có thể tham khảo thêm một số bài viết đã có trên Taimienphi.vn để tìm hiểu thêm về hàm TRIM, hàm DIR trong VBA nhé.
1. Ví dụ về Autofilter trong VBA
Dưới đây là một số ví dụ về cách sử dụng Autofilter trong VBA:
1.1 Ví dụ 1: Lọc dữ liệu dựa trên điều kiện Text
Giả sử bạn có một file dữ liệu như dưới đây và muốn lọc dữ liệu dựa trên cột Item:
Đoạn mã dưới đây sẽ lọc tất cả các hàng, trong đó các mục là Printer:
Sub FilterRows()
Worksheets("Sheet1").Range("A1").AutoFilter Field:=2, Criteria1:="Printer"
End Sub
Đoạn mã trên tham chiếu đến bảng tính 1 và trong bảng tính nó tham chiếu đến ô A1 (là ô trong tập dữ liệu).
Lưu ý trong ví dụ này chúng ta sử dụng Field:=2, vì cột Item là cột thứ 2 trong tập dữ liệu, tính từ trái sang.
1.2 Ví dụ 2: Lọc dữ liệu theo nhiều tiêu chí (AND / OR) trong cùng một cột
Giả sử chúng ta có cùng một tập dữ liệu và muốn lọc tất cả các bản ghi trong đó các mục là Printer hoặc Projector:
Để làm được điều này, chúng ta sẽ sử dụng đoạn mã dưới đây:
Sub FilterRowsOR()
Worksheets("Sheet1").Range("A1").AutoFilter Field:=2, Criteria1:="Printer", Operator:=xlOr, Criteria2:="Projector"
End Sub
Lưu ý, trong đoạn mã trên chúng ta sử dụng toán tử xlOR. Điều này để VBA sử dụng cả 2 tiêu chí và lọc dữ liệu nếu bất kỳ một trong hai tiêu chí được đáp ứng.
Tương tự, chúng ta cũng có thể sử dụng tiêu chí AND.
Cho ví dụ, nếu muốn lọc tất cả các bản ghi có số lượng lớn hơn 10 nhưng dưới 20, chúng ta có thể sử dụng đoạn mã dưới đây:
Sub FilterRowsAND()
Worksheets("Sheet1").Range("A1").AutoFilter Field:=4, Criteria1:=">10", _
Operator:=xlAnd, Criteria2:="<>
End Sub
1.3 Ví dụ 3: Lọc dữ liệu theo nhiều tiêu chí nhưng khác cột
Giả sử chúng ta có file dữ liệu dưới đây:
Với Autofilter, chúng ta có thể lọc nhiều cột cùng một lúc.
Cho ví dụ, nếu muốn lọc tất cả các bản ghi trong đó mục Printer và Sales Rep là Mark, chúng ta có thể sử dụng đoạn mã dưới đây:
Sub FilterRows()
With Worksheets("Sheet1").Range("A1")
.AutoFilter field:=2, Criteria1:="Printer"
.AutoFilter field:=3, Criteria1:="Mark"
End With
End Sub
1.4 Ví dụ 4: Sử dụng Autofilter để lọc dữ liệu Top 10
Giả sử chúng ta có bảng dữ liệu dưới đây:
Sử dụng đoạn mã dưới đây để lọc top 10 bản ghi hàng đầu (dựa trên cột Quantity):
Sub FilterRowsTop10()
ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="10", Operator:=xlTop10Items
End Sub
Trong ví dụ trên, bảng tính có tên là ActiveSheet. Bạn có thể thay thế bằng tên bảng tính của mình.
Lưu ý, trong ví dụ trên nếu muốn top 5 mục hàng đầu, chúng ta chỉ cần thay đổi số trong Criteria1:="10'' từ 10 thành 5.
Mã sử dụng để lấy top 5 mục hàng đầu có dạng:
Sub FilterRowsTop5()
ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="5", Operator:=xlTop10Items
End Sub
Bất kể chúng ta muốn lấy bao nhiêu mục top đầu, giá trị toán tử (Operator) luôn luôn là xlTop10Items.
Tương tự, để lấy top 10 mục thấp nhất, chúng ta sử dụng đoạn mã dưới đây:
Sub FilterRowsBottom10()
ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="10", Operator:=xlBottom10Items
End Sub
1.5 Ví dụ 5: Lọc 10% dữ liệu bằng phương thức AutoFilter
Chúng ta vẫn tiếp tục sử dụng bảng dữ liệu trong phần ví dụ trên.
Sử dụng đoạn mã dưới đây để lấy 10% các bản ghi hàng đầu (dựa trên cột Quantity):
Sub FilterRowsTop10()
ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="10", Operator:=xlTop10Percent
End Sub
Vì trong bảng dữ liệu của chúng ta có 20 bản ghi, đoạn mã trên sẽ trả về 2 bản ghi hàng đầu (chiếm 10% tổng số bản ghi).
1.6 Ví dụ 6: Sử dụng ký tự đại diện trong Autofilter
Cho bảng dữ liệu như dưới đây:
Để lọc tất cả các hàng có tên các mục chứa từ Board, chúng ta sử dụng đoạn mã dưới đây:
Sub FilterRowsWildcard()
Worksheets("Sheet1").Range("A1").AutoFilter Field:=2, Criteria1:="*Board*"
End Sub
Trong đoạn mã trên, chúng ta sử dụng ký tự đại diện * (dấu hoa thị) trước và sau từ Board (là tiêu chí).
Một dấu hoa thị có thể đại diện cho số lượng ký tự bất kỳ. Vì vậy đoạn mã trên sẽ lọc bất kỳ mục nào có từ Board trong đó.
1.7 Ví dụ 7: Sao chép các hàng đã lọc vào bảng tính mới
Nếu muốn lọc các bản ghi dựa trên các tiêu chí và sao chép các hàng đã lọc, chúng ta có thể sử dụng macro dưới đây. Macro sẽ sao chép các hàng đã được lọc, thêm một bảng tính mới, sau đó dán các hàng được lọc vào bảng tính mới:
Sub CopyFilteredRows()
Dim rng As Range
Dim ws As Worksheet
If Worksheets("Sheet1").AutoFilterMode = False Then
MsgBox "khong co hang nao duoc loc"
Exit Sub
End If
Set rng = Worksheets("Sheet1").AutoFilter.Range
Set ws = Worksheets.Add
rng.Copy Range("A1")
End Sub
Đoạn mã trên sẽ kiểm tra xem có hàng nào được lọc trong Sheet1 hay không. Nếu không có hàng nào được lọc, nó sẽ hiển thị hộp thoại thông báo.
Nếu có các hàng được lọc, nó sẽ sao chép các hàng đó, chèn một bảng tính mới và dán các hàng đã lọc vào bảng tính mới được chèn.
1.8 Ví dụ 8: Sao chép các cột được lọc vào bảng tính mới
Nếu muốn lọc các bản ghi dựa trên các tiêu chí và sao chép các cột đã lọc, chúng ta có thể sử dụng macro dưới đây. Macro sẽ sao chép các cột đã được lọc, thêm một bảng tính mới, sau đó dán các cột này vào bảng tính mới:
Sub CopyFilteredRows()
Dim rng As Range
Dim ws As Worksheet
If Worksheets("Sheet1").AutoFilterMode = False Then
MsgBox "khong co cot nao duoc loc"
Exit Sub
End If
Set rng = Worksheets("Sheet1").AutoFilter.Range
Set ws = Worksheets.Add
rng.Copy Range("A1")
End Sub
Đoạn mã trên sẽ kiểm tra xem có cột nào được lọc trong Sheet1 hay không. Nếu không có cột nào được lọc, nó sẽ hiển thị hộp thoại thông báo.
Nếu có các cột được lọc, nó sẽ sao chép các cột đó, chèn một bảng tính mới và dán các cột đã lọc vào bảng tính mới được chèn.
1.9 Ví dụ 9: Lọc dữ liệu dựa trên giá trị ô
Bằng cách sử dụng Autofilter trong VBA cùng menu thả xuống, chúng ta có thể tạo một hàm trong đó khi chọn một mục trong menu, tất cả các bản ghi của mục đó sẽ được lọc.
Kiểu cấu trúc này có thể hữu ích trong trường hợp nếu muốn lọc dữ liệu nhanh chóng, sau đó sử dụng cho các tác vụ khác.
Để làm được điều này, chúng ta sử dụng đoạn mã dưới đây:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then
If Range("B2") = "All" Then
Range("A5").AutoFilter
Else
Range("A5").AutoFilter Field:=2, Criteria1:=Range("B2")
End If
End If
End Sub
Nó là mã sự kiện bảng tính, chỉ được thực thi khi có thay đổi trong bảng tính và ô đích là B2 (nơi chúng ta có menu thả xuống).
Ngoài ra nếu điều kiện If Then Else được sử dụng để kiểm tra xem người dùng đã chọn All từ menu hay chưa. Nếu All được chọn, toàn bộ dữ liệu sẽ được hiển thị.
Lưu ý mã này không được đặt trong module. Thay vào đó chúng ta đặt trong backend của bảng tính có dữ liệu này.
Thực hiện theo các bước dưới đây để đặt mã trong cửa sổ mã bảng tính:
Bước 1: Mở VB Editor (bằng cách sử dụng phím tắt Alt + F11).
Bước 2: Trong bảng Project Explorer, kích đúp chuột vào tên bảng tính mà bạn muốn áp dụng hàm lọc này.
Bước 3: Trên cửa sổ mã bảng tính, sao chép và dán đoạn mã ở trên vào.
Bước 4: Đóng cửa sổ VB Editor.
Từ giờ khi sử dụng menu thả xuống, nó sẽ tự động lọc dữ liệu.
https://thuthuat.taimienphi.vn/su-dung-autofilter-trong-vba-phan-2-45661n.aspx
Bài viết Sử dụng Autofilter trong VBA (Phần 2) trên đây Taimienphi.vn vừa giới thiệu cho bạn một số ví dụ về cách sử dụng Autofilter trong VBA. Nếu có bất kỳ thắc mắc hoặc câu hỏi nào, bạn đọc có thể để lại ý kiến của mình trong phần bình luận bên dưới bài viết.