Hướng dẫn này sẽ trình bày cách loại bỏ các bản sao bằng phương pháp RemoveDuplicates trong VBA.
Phương thức RemoveDuplicates
Khi dữ liệu được nhập hoặc dán vào trang tính Excel, nó thường có thể chứa các giá trị trùng lặp. Bạn có thể cần phải làm sạch dữ liệu đến và loại bỏ các bản sao.
May mắn thay, có một phương pháp dễ dàng trong đối tượng Phạm vi của VBA cho phép bạn thực hiện điều này.
| 1 | Phạm vi (“A1: C8”). Các cột RemoveDuplicates: = 1, Header: = xlYes |
Cú pháp là:
RemoveDuplicates ([Cột], [Header]
- [Cột] - Chỉ định cột nào được kiểm tra các giá trị trùng lặp. Tất cả các cột khớp nhiều để được coi là trùng lặp.
- [Tiêu đề] - Dữ liệu có tiêu đề không? xlNo (mặc định), xlYes, xlYesNoGuess
Về mặt kỹ thuật, cả hai thông số đều là tùy chọn. Tuy nhiên, nếu bạn không chỉ định đối số Cột, sẽ không có đối số trùng lặp nào bị xóa.
Giá trị mặc định cho Header là xlNo. Tất nhiên, tốt hơn là chỉ định đối số này, nhưng nếu bạn có một hàng tiêu đề, thì không chắc hàng tiêu đề sẽ khớp như một bản sao.
Ghi chú sử dụng RemoveDuplicates
- Trước khi sử dụng phương thức RemoveDuplicates, bạn phải chỉ định một phạm vi sẽ được sử dụng.
- Phương thức RemoveDuplicates sẽ loại bỏ bất kỳ hàng nào có các bản sao được tìm thấy, nhưng sẽ giữ hàng ban đầu với tất cả các giá trị.
- Phương thức RemoveDuplicates chỉ hoạt động trên cột chứ không phải trên hàng, nhưng mã VBA có thể được viết để khắc phục tình trạng này (xem sau).
Dữ liệu mẫu cho các ví dụ VBA
Để hiển thị cách mã mẫu hoạt động, dữ liệu mẫu sau được sử dụng:
Loại bỏ các hàng trùng lặp
Mã này sẽ xóa tất cả các hàng trùng lặp chỉ dựa trên các giá trị trong cột A:
| 123 | Sub RemoveDupsEx1 ()Phạm vi (“A1: C8”). Các cột RemoveDuplicates: = 1, Header: = xlYesKết thúc Sub |
Lưu ý rằng chúng tôi đã xác định rõ ràng Phạm vi “A1: C8”. Thay vào đó, bạn có thể sử dụng usedRange. UsedRange sẽ xác định hàng và cột được sử dụng cuối cùng trong dữ liệu của bạn và áp dụng RemoveDuplicates cho toàn bộ phạm vi đó:
| 123 | Sub RemoveDups_UsedRange ()ActiveSheet.UsedRange.RemoveDuplicates Columns: = 1, Header: = xlYesKết thúc Sub |
UsedRange cực kỳ hữu ích, giúp bạn không cần phải xác định phạm vi một cách rõ ràng.
Sau khi chạy mã này, trang tính của bạn bây giờ sẽ giống như sau:
Lưu ý rằng vì chỉ cột A (cột 1) được chỉ định, bản sao ‘Táo’ trước đây ở hàng 5 đã bị loại bỏ. Tuy nhiên, Số lượng (cột 2) thì khác.
Để loại bỏ trùng lặp, so sánh nhiều cột, chúng ta có thể chỉ định các cột đó bằng phương thức Array.
Loại bỏ các bản sao so sánh nhiều cột
| 123 | Sub RemoveDups_MultColumns ()ActiveSheet.UsedRange.RemoveDuplicates Columns: = Array (1, 2), Header: = xlYesKết thúc Sub |
Mảng yêu cầu VBA so sánh dữ liệu bằng cách sử dụng cả cột 1 và 2 (A và B).
Các cột trong mảng không nhất thiết phải theo thứ tự liên tiếp.
| 123 | Sub SimpleExample ()ActiveSheet.UsedRange.RemoveDuplicates Columns: = Array (3, 1), Header: = xlYesKết thúc Sub |
Trong ví dụ này, cột 1 và 3 được sử dụng để so sánh trùng lặp.
Ví dụ mã này sử dụng tất cả ba cột để kiểm tra các bản sao:
| 123 | Sub SimpleExample ()ActiveSheet.UsedRange.RemoveDuplicates Columns: = Array (1, 2, 3), Header: = xlYesKết thúc Sub |
Xóa các hàng trùng lặp khỏi bảng
RemoveDuplicates cũng có thể được áp dụng cho bảng Excel theo cách giống hệt như vậy. Tuy nhiên, cú pháp hơi khác một chút.
| 1234 | Sub SimpleExample ()ActiveSheet.ListObjects ("Table1"). DataBodyRange.RemoveDuplicates Cột: = Array (1, 3), _Tiêu đề: = xlYesKết thúc Sub |
Điều này sẽ loại bỏ các bản sao trong bảng dựa trên cột 1 và 3 (A và C). Tuy nhiên, nó không làm gọn gàng định dạng màu của bảng và bạn sẽ thấy các hàng trống có màu bị bỏ lại ở cuối bảng.
Loại bỏ các bản sao khỏi mảng
Nếu bạn cần xóa các giá trị trùng lặp khỏi một mảng, tất nhiên bạn có thể xuất mảng của mình sang Excel, sử dụng phương thức RemoveDuplicates và nhập lại mảng.
Tuy nhiên, chúng tôi cũng đã viết một thủ tục VBA để loại bỏ các bản sao khỏi một mảng.
Loại bỏ các bản sao khỏi các dòng dữ liệu bằng VBA
Phương thức RemoveDuplicates chỉ hoạt động trên các cột dữ liệu, nhưng với một số suy nghĩ ‘out of the box’, bạn có thể tạo một thủ tục VBA để xử lý các hàng dữ liệu.
Giả sử rằng dữ liệu của bạn trông giống như thế này trên trang tính của bạn:
Bạn có các bản sao giống như trước đây trong cột B và E, nhưng bạn không thể loại bỏ chúng bằng phương pháp RemoveDuplicates.
Câu trả lời là sử dụng VBA để tạo một trang tính bổ sung, sao chép dữ liệu vào đó để chuyển đổi nó thành các cột, loại bỏ các bản sao và sau đó sao chép nó để chuyển đổi nó trở lại thành các hàng.
| 12345678910111213141516171819202122232425262728293031323334353637 | Sub DuplicatesInRows ()'Tắt cập nhật màn hình và cảnh báo - chúng tôi muốn mã chạy trơn tru mà người dùng không nhìn thấy‘Chuyện gì đang xảy ra vậyApplication.ScreenUpdating = SaiApplication.DisplayAlerts = False'Thêm một trang tính mớiSheets.Add After: = ActiveSheet'Gọi trang tính mới' CopySheet 'ActiveSheet.Name = "CopySheet"'Sao chép dữ liệu từ trang tính gốcTrang tính ("DataInRows"). UsedRange.Copy'Kích hoạt trang tính mới đã được tạoTrang tính ("CopySheet"). Kích hoạt'Dán chuyển vị dữ liệu để dữ liệu hiện ở trong các cộtActiveSheet.Range ("A1"). PasteSpecial Paste: = xlPasteAll, Operation: = xlNone, SkipBlanks: = _Sai, Transpose: = Đúng'Loại bỏ các bản sao cho cột 1 và 3ActiveSheet.UsedRange.RemoveDuplicates Columns: = Array (1, 3), Header _: = xl Có'Xóa dữ liệu trong trang tính gốcTrang tính ("DataInRows"). UsedRange.ClearContents'Sao chép các cột dữ liệu từ trang tính mới được tạoSheets ("Copysheet"). UsedRange.Copy'Kích hoạt trang tính gốcTrang tính ("DataInRows"). Kích hoạt'Dán chuyển vị dữ liệu không trùng lặpActiveSheet.Range ("A1"). PasteSpecial Paste: = xlPasteAll, Operation: = xlNone, SkipBlanks: = _Sai, Transpose: = Đúng'Xóa trang sao chép - không cần thiết nữaTrang tính ("Bản sao"). Xóa'Kích hoạt trang tính gốcTrang tính ("DataInRows"). Kích hoạt'Bật lại tính năng cập nhật màn hình và cảnh báoApplication.ScreenUpdating = TrueApplication.DisplayAlerts = TrueKết thúc Sub |
Mã này giả định rằng dữ liệu gốc trong các hàng được giữ trên một trang tính có tên là 'DataInRows'
Sau khi chạy mã, trang tính của bạn sẽ giống như sau:
Bản sao ‘Táo’ trong cột E hiện đã bị loại bỏ. Người dùng đã trở lại vị trí sạch sẽ, không có bảng tính thừa nào bị treo xung quanh và toàn bộ quá trình đã được thực hiện trơn tru mà không có màn hình nhấp nháy hoặc thông báo cảnh báo.
