Sử dụng Autofilter trong VBA (Phần 2)

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é.

Mục Lục bài viết:

1. Ví dụ về Autofilter trong VBA.

1.1. Ví dụ 1: Lọc dữ liệu dựa trên điều kiện Text

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

1.3. Ví dụ 3: Lọc dữ liệu theo nhiều tiêu chí nhưng khác cột

1.4. Ví dụ 4: Sử dụng Autofilter để lọc dữ liệu Top 10

1.5. Ví dụ 5: Lọc 10% dữ liệu bằng phương thức AutoFilter

1.6. Ví dụ 6: Sử dụng ký tự đại diện trong Autofilter

1.7. Ví dụ 7: Sao chép các hàng đã lọc vào bảng tính mới

1.8. Ví dụ 8: Sao chép các cột được lọc vào bảng tính mới

1.9. Ví dụ 9: Lọc dữ liệu dựa trên giá trị ô

 

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:

su dung autofilter trong vba phan 2

Đ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:

su dung autofilter trong vba phan 2 2

Để 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:

su dung autofilter trong vba phan 2 3

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:

su dung autofilter trong vba phan 2 4

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:

su dung autofilter trong vba phan 2 5

Để 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.

su dung autofilter trong vba phan 2 6

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.

su dung autofilter trong vba phan 2 7

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.

su dung autofilter trong vba phan 2 8

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.

http://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.

Tác giả: Cao Toàn Mỹ     (4.3★- 4 đánh giá)  ĐG của bạn?

  

Bài viết liên quan

Excel - Cách mở VBA trong Excel
Excel - Vòng lặp For trong VBA
Tất tần tật về VBA trong Excel (Phần 2)
Cách xử lý lỗi VBA bằng các lệnh On Error
Tất tần tật về VBA trong Excel (Phần 3)
Từ khoá liên quan:

Sử dụng Autofilter trong VBA

, Autofilter trong VBA, Autofilter,

SOFT LIÊN QUAN
  • VBA cho Powerpoint

    Hướng dẫn viết mã VBA trong Powerpoint

    VBA cho Powerpoint là tài liệu giới thiệu về VBA và Powerpoint, cách sử dụng VBA trong Powerpoint nhằm tăng tính điều khiển cho ứng dụng này. VBA là ngôn ngữ lập trình có thể chạy trong ứng dụng chủ với chức năng hỗ trợ ...

Tin Mới


 Mùa hè tới rồi muốn so sánh giá tìm điều hòa giá rẻ nhất thị trường hãy dùng TopGia để so sanh gia có nhiều mẫu dieu hoa để so sanh sanh, xem dieu hoagiá rẻ để so sánh giá