Alt+F11進入編輯器,插入模塊,復制粘貼下面代碼:
Sub?t() Dim?str?As?String'保存每壹行內容的臨時變量 Dim?colNum?As?Integer'表格列數 Dim?rowNum?As?Long'表格行數 Dim?dict'字典 Set?dict?=?CreateObject("Scripting.Dictionary") With?ActiveSheetcolNum?=?.UsedRange.Columns.Count
rowNum?=?.UsedRange.Rows.Count
For?i?=?1?To?rowNum
str?=?""
For?j?=?1?To?colNum
str?=?str?&?.Cells(i,?j).Value'將每壹行的值連接成壹個字符串Next
If?Not?dict.Exists(str)?Then
dict.Add?str,?i'如果字典中不存在str的值,加入字典,Key為str的值,Value為行號Else
.Cells(i,?colNum?+?1).Value?=?"與第["?&?dict(str)?&?"]行重復"'如果存在重復值,在表格右側空白列添加標註 .Cells(dict(str),?colNum?+?1).Value?=?"與第["?&?i?&?"]行重復"'同時,在之前行右側也添加標註End?If
Next
End?WithEnd?Sub
效果如圖: