|
发表于 2016-12-13 09:00
|
显示全部楼层
本楼为最佳答案
- Sub 多列标识重复数据()
- Dim arr, i&, j&
- On Error Resume Next
- Cells.Interior.Pattern = xlNone '选择范围填充为无色
- Set Rng = Application.InputBox(prompt:="请选择要标识重复数据的区域", Title:="提示", Default:="请选择", Type:=8)
- If Rng Is Nothing Then Exit Sub
- Set Rng = Application.Intersect(Rng, ActiveSheet.UsedRange)
- arr = Rng
- xColor = 6 '标识的颜色
- Set dic = CreateObject("scripting.dictionary")
- Set d = CreateObject("scripting.dictionary")
- For i = 1 To UBound(arr) '遍历数组arr
- s = ""
- For j = 1 To UBound(arr, 2)
- s = s & arr(i, j)
- Next
- d(s) = d(s) & "," & Rng(i, j).Row
- For j = 1 To UBound(arr, 2)
- dic(s) = dic(s) & "," & Rng(i, j).Address(0, 0)
- Next
- Next i
- For Each s In dic.keys
- If InStr(Mid(d(s), 2), ",") > 0 Then
- Range(Mid(dic(s), 2)).Interior.ColorIndex = xColor '标识颜色
- msg = msg & Chr(10) & "第" & Mid(d(s), 2) & "行重复"
- End If
- Next
- If Len(msg) Then MsgBox Mid(msg, 2) Else MsgBox "无重复行"
- End Sub
复制代码 |
评分
-
查看全部评分
|