Khi dữ liệu được nhập hoặc dán vào một 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 dữ liệu trùng lặp
May mắn thay, có một phương thức dễ dàng trong đối tượng Range của VBA cho phép bạn thực hiện điều này
Range[“A1:C8”].RemoveDuplicates Columns:=1, Header:=xlYes
Cú pháp là
RemoveDuplicates[[Cột],[Tiêu đề]
- [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 phù hợp nhiều để được coi là trùng lặp
- [Header] – Dữ liệu có tiêu đề không?
Về mặt kỹ thuật, cả hai tham 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ó mục trùng lặp nào bị xóa
Giá trị mặc định cho Tiêu đề là xlNo. Tất nhiên, tốt hơn là bạn nên 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 dưới dạng trùng lặp
Loại bỏ trùng lặp Ghi chú sử dụng
- 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ẽ xóa mọi hàng có trùng lặp được tìm thấy, nhưng sẽ giữ lại hàng gốc 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 hoạt động 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ụ về VBA
Để hiển thị cách mã ví dụ hoạt động, dữ liệu mẫu sau đây được sử dụng
Xóa 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
Sub RemoveDupsEx1[]
Range[“A1:C8”].RemoveDuplicates Columns:=1, Header:=xlYes
End 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. Phạm vi đã sử dụng 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 đó
Sub RemoveDups_UsedRange[]
ActiveSheet.UsedRange.RemoveDuplicates Columns:=1, Header:=xlYes
End Sub
usedRange cực kỳ hữu ích, loại bỏ nhu cầu bạn xác định rõ ràng phạm vi
Sau khi chạy các mã này, trang tính của bạn sẽ trông như thế này
Lưu ý rằng vì chỉ có cột A [cột 1] được chỉ định, bản sao 'Táo' trước đây ở hàng 5 đã bị xóa. Tuy nhiên, Số lượng [cột 2] lại khác
Để loại bỏ các cột 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ỏ trùng lặp So sánh nhiều cột
Sub RemoveDups_MultColumns[]
ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array[1, 2] , Header:=xlYes
End Sub
Mảng yêu cầu VBA so sánh dữ liệu bằng cả hai 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
Sub SimpleExample[]
ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array[3, 1] , Header:=xlYes
End 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 cả ba cột để kiểm tra các bản sao
Sub SimpleExample[]
ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array[1, 2, 3] , Header:=xlYes
End 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ùng một cách. Tuy nhiên, cú pháp hơi khác một chút
Sub SimpleExample[]
ActiveSheet.ListObjects["Table1"].DataBodyRange.RemoveDuplicates Columns:=Array[1, 3], _
Header:=xlYes
End 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 sắp xếp đị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
Lập trình VBA dễ dàng
Ngừng tìm kiếm mã VBA trực tuyến. Tìm hiểu thêm về AutoMacro - Trình tạo mã VBA cho phép người mới bắt đầu viết mã quy trình từ đầu với kiến thức mã hóa tối thiểu và có nhiều tính năng tiết kiệm thời gian cho tất cả người dùng.Tìm hiểu thêm.
Xóa các bản sao khỏi mảng
Nếu bạn cần loại bỏ 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 hà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ĩ 'ngoài luồng', 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 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 trong cột B và E, nhưng bạn không thể xóa 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 lại chuyển đổi nó trở lại các hàng
Sub DuplicatesInRows[]
'Turn off screen updating and alerts – we want the code to run smoothly without the user seeing
‘what is going on
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'Add a new worksheet
Sheets.Add After:=ActiveSheet
'Call the new worksheet 'CopySheet'
ActiveSheet.Name = "CopySheet"
'Copy the data from the original worksheet
Sheets["DataInRows"].UsedRange.Copy
'Activate the new sheet that has been created
Sheets["CopySheet"].Activate
'Paste transpose the data so that it is now in columns
ActiveSheet.Range["A1"].PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
'Remove the duplicates for columns 1 and 3
ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array[1, 3], Header _
:=xlYes
'Clear the data in the original worksheet
Sheets["DataInRows"].UsedRange.ClearContents
'Copy the columns of data from the new worksheet created
Sheets["Copysheet"].UsedRange.Copy
'Activate the original sheet
Sheets["DataInRows"].Activate
'Paste transpose the non-duplicate data
ActiveSheet.Range["A1"].PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
'Delete the copy sheet - no longer needed
Sheets["Copysheet"].Delete
'Activate the original sheet
Sheets["DataInRows"].Activate
'Turn back on screen updating and alerts
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End 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ẽ trông như thế này
Bản sao 'Táo' trong cột E hiện đã bị xóa. Người dùng đã trở lại ở một vị trí sạch sẽ, không có bảng tính không liên quan 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