Lọc danh sách không trùng là 1 chủ đề khá thú vị trong Excel. Có rất nhiều cách để làm việc này. Tuy nhiên làm thế nào để danh sách không trùng được lọc ra sẽ tự động cập nhật khi thêm / bớt dữ liệu trong danh sách gốc? Nếu dùng công thức sẽ rất phức tạp. Và Học Excel Online sẽ giới thiệu với các bạn một cách rất đơn giản để làm việc đó 1 cách tự động, đó là sử dụng Advanced Filter kết hợp với VBA

Để giúp các bạn dễ hình dung thì chúng ta cùng thực hiện yêu cầu sau:

Trong bảng dữ liệu trên, cột Tên mặt hàng có rất nhiều mặt hàng và có thể lặp lại hoặc thêm mới những mặt hàng khác. Và yêu cầu của chúng ta là:

• Lọc ra 1 danh sách tên các mặt hàng không trùng (chỉ xuất hiện 1 lần trong danh sách)
• Sau mỗi lần thay đổi nội dung ở cột Tên mặt hàng thì danh sách không trùng đó sẽ được cập nhật lại.

Cách thực hiện như sau:

1. Record macro cho thao tác lọc danh sách không trùng bằng cách sử dụng advanced filter

Để có thể thực hiện thao tác record macro trước hết các bạn cần lưu ý:

• File của bạn sẽ có sử dụng macro. Khi lưu file chú ý lưu ở định dạng Workbook enable macro (.xlsm)
• Mở thẻ developer trong Excel để sử dụng được thao tác record macro

Trong thẻ developer chọn Record macro tại:

Tiếp theo  chúng ta sẽ thực hiện ghi lại thao tác lọc danh sách không trùng bằng Advanced Filter

Trong thao tác sử dụng advanced filter này chúng ta chú ý:

• Action: chọn copy to another location để copy danh sách ra 1 vị trí khác
• List range: vùng danh sách cần lọc trùng. Ở đây phải chọn từ tên tiêu đề (dòng thứ 1)
• Criteria range: để trống
• Copy to: chọn 1 vị trí cần xuất danh sách đó ra
• Unique records only: đánh dấu chọn mục này để chỉ lấy những dữ liệu không trùng

Sau khi lọc ra vị trí ô G1 chúng ta được kết quả là danh sách tên các mặt hàng không trùng.

Kết thúc việc record macro, chúng ta trở lại thẻ developer và chọn Stop recording

Cùng kiểm tra câu lệnh VBA vừa record được:

Sub Macro1()

Range(“C1:C19”).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range(“G1”), Unique:=True

End Sub

2. Tự động cập nhật danh sách mỗi khi dữ liệu thay đổi

 a. Thay đổi phạm vi vùng dữ liệu

Trong câu lệnh macro thì vùng Range(“C1:C19”) là vùng dữ liệu ban đầu. Chúng ta có thể xác định rõ hơn với 2 yếu tố:

• Tên sheet chứa vùng đó
• Phạm vi vùng

Do vùng này đặt tại sheet1 nên ta có thể viết lại như sau:

Sheet1.Range(“C1:C100”)

Thay vì chỉ áp dụng từ C1 tới C19 thì chúng ta có thể mở rộng sẵn vùng này

b. Thay đổi vị trí xuất kết quả

Kết quả danh sách được trích xuất là Range(“G1”).

Chúng ta có thể thay đổi sang Sheet khác, vị trí khác. Ví dụ đặt tại Sheet2, tại ô A1

Sheet2.Range(“A1”)

Câu lệnh có thể viết lại là:

Sub Macro1()

Sheet1.Range(“C1:C100”).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheet2.Range(“A1”), Unique:=True

End Sub

c. Gắn macro vào sự kiện thay đổi trong bảng dữ liệu

Viết lệnh với sự kiện worksheet.change tại sheet1 như sau:

Ý nghĩa của câu lệnh trên là: Khi Sheet1 có thay đổi trong vùng dữ liệu C2:C100 thì sẽ gọi ra câu lệnh Macro1 – chính là câu lệnh lọc dữ liệu theo danh sách không trùng.

Xem thêm: Sự kiện Worksheet_change trong VBA

Như vậy là chúng ta đã hoàn thành được cách ứng dụng Advanced Filter trong VBA để có thể tự động lọc danh sách không trùng.

3. Nâng cao

Tuy nhiên trong câu lệnh trên cũng chứa nhiều điểm hạn chế:

• Bảng dữ liệu gốc có chứa dòng trống sẽ khiến xuất hiện dòng trống trong danh sách được lọc.
• Không được thay đổi tiêu đề của cột dữ liệu lọc tại bảng dữ liệu và tại vị trí kết quả danh sách được lọc.

Để giải quyết 2 vấn đề trên thì chúng ta có thể tìm hiểu thêm 1 số phương pháp sau:

• Thêm câu lệnh sắp xếp dữ liệu trong danh sách sau khi lọc. Việc sắp xếp dữ liệu sẽ đẩy dòng trống xuống dưới, tránh việc có dòng trống xen kẽ trong danh sách.
• Thêm câu lệnh xóa toàn bộ vùng kết quả danh sách được lọc, bao gồm cả tên tiêu đề trước mỗi lần cập nhật danh sách. Như vậy sẽ không mắc lỗi tên tiêu đề không phù hợp.

Để tìm hiểu thêm nhiều kiến thức về VBA, mời bạn tham dự khóa học VBA101 – Tự động hóa Excel với lập trình VBA. Đây là khóa học rất hữu ích dành cho bạn, giúp bạn hiểu một cách bài bản về VBA và cách sử dụng VBA, ứng dụng VBA vào công việc để tăng hiệu quả. Hãy cùng khám phá nào: